Databases
StackOverflow2013
Postsdbo
POGet random values from a row while two values that exist are not selected together
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POGet random values from a row while two values that exist are not selected together
    primarykey
    data
    text
    <p>I'm trying to do something I think rather tricky and can't wrap my head around it. Kinda driving me nuts. Seems like it should be easier than it is.</p> <p>So I have two tables. I display two items together randomly. When you pick an item, it gets entered into the DB as a vote for that item with the <code>item ID</code> stored in <code>win_id</code> column and the other item goes into the <code>lst_id</code> column. Then it displays two more random items and so on and so on. Now my problem is once they choose an item, I never want those two pair to be shown again to that user. The items can be shown with other items that haven't been voted on against however. Hope this is making since. This is the query I'm trying unsuccessfully.</p> <pre><code>SELECT * FROM (SELECT be.media_id FROM battle_entries as be WHERE be.btype=1 AND be.mature=1 AND be.mem_id!=1 AND NOT EXISTS (SELECT * FROM battle_votes as bv WHERE bv.win_id = be.media_id AND bv.mem_id=1 AND bv.lst_id=be.media_id)) s ORDER BY RANDOM() LIMIT 2"; </code></pre> <p>The <code>win_id</code> and the <code>lst_id</code> are the pair that has been chosen by the user. I never want those two be shown together to the same user again, if they exist in the battle_votes table by that user, but still being randomly selected with other items that haven't been shown together. </p> <p>Here are the tables </p> <p>battle_entries</p> <pre><code>---------------------------------------------------- | btl_id | mem_id | posted | media_id | active | ---------------------------------------------------- </code></pre> <p>battle_votes</p> <pre><code>---------------------------------------------------- | btl_id | mem_id | win_id | lst_id | posted | ---------------------------------------------------- </code></pre> <p>If there is a better way to do it with a different schema, I'm open to that too if this is not possible. </p> <p><strong>Edit:</strong> Best I can explain it again.</p> <p>My query selects 2 random items that the user has not voted on. Once the user chooses one, a new row is created in the battle_votes table. The chosen <code>item ID</code> is entered into the <code>win_id</code> column and the not chosen goes into the <code>lst_id</code> column. <code>This is a single row.</code> Now the next time it does a random select to show two more items, I don't want those 2 to be shown again as a <code>pair</code>, but each one can be shown with another item that hasn't entered into the battle_votes table as a <code>pair</code>. I know it might be confusing.</p> <p>Basically I don't want the same pair to ever show again together, if they have chosen either item. </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USPrix
    1. USPanama Jack
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COAre you re-inventing Facebook from scratch? ;) (might not make sense to those who haven't seen The Social Network or read much about the history of fb)
      singulars
      1. POGet random values from a row while two values that exist are not selected together
      1. USHelena
    2. CODepending on the complexity of your database and also on the options available (I've never worked with Postgre) one of the suggestions might be to generate a random pair the way you're doing it (without all the conditions, just order all `media_id`'s randomly and take the top 2), save them into two variables and then check if this pair is already in the `battle_votes` table (in either order); if yes throw it away and generate a new pair. This is already some SQL programming however - not sure if you have the option of writing and running stored procedures.
      singulars
      1. POGet random values from a row while two values that exist are not selected together
      1. USHelena
    3. CO@Helena lol No not trying to reinvent but I know what you're talking about. I thought about having two queries or using a loop do until loop but I thought it might be expensive. I might have to go that route. I seems I've stumped some of the SQL pro's around or either they don't want to answer. :)
      singulars
      1. POGet random values from a row while two values that exist are not selected together
      1. USPanama Jack
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload