2

Below are two tables with some sample data :-

Uploads:  
id: 1 , file_ref:abc  
id: 2, file_ref: abc1  
id: 4, file_ref: abc3  
id: 5, file_ref: abc4  
id: 6, file_ref: abc5  
id: 7, file_ref: abc6  

media:  
id: 3, name: 'My Doc' , type: doc  
id: 6, name: 'My Img' , type: img

I have these 2 models in rails. Now in my controller I want to get all records in uploads if their id exists in the media table i.e. records from upload table with ids 3 and 6.

MZaragoza
  • 10,108
  • 9
  • 71
  • 116
Abdul Haseeb
  • 370
  • 5
  • 14
  • 1
    The id is shared? So the only record to return is the `id: 6, file_ref: abc5 `, because the `id 3` does not exists in the table media? – MatayoshiMariano Aug 07 '17 at 15:00
  • can you change the value of ** abc5** to be **5** or do you have to first get the number out and then find – MZaragoza Aug 07 '17 at 15:40
  • All media would exist in the uploads, but the file_ref is only available in the uploads which is what I want. So I have to get both records with id 6 and 3 from the uploads table. – Abdul Haseeb Aug 07 '17 at 15:40

3 Answers3

1

You can do a subquery in your call

I think that it should look something like this

Upload.where(media_id: Media.all.map(&:id))

I hope that it helps

MZaragoza
  • 10,108
  • 9
  • 71
  • 116
0

Have you set up a relationship between them? From the sounds of it you haven't (you really should), but if you haven't then try:

ids = Media.all.ids
Upload.where(id: ids)

or as one line

Upload.where(id: Media.all.ids)

This will return all records in your upload table that has an ID that exists in the media table.

Mark
  • 6,112
  • 4
  • 21
  • 46
  • Mark, the disadvantage of doing this, is that you are doing two queries to the database, one for the `Media.all.ids` and one for the `Upload.where`. With the INNER JOIN you are doing only one. – MatayoshiMariano Aug 07 '17 at 15:05
  • 1
    `Upload.where(id: Media.all)` will do a single SQL query with a nested `SELECT medias.id [...] FROM medias` -- but that is weird to assume that Media <-> Upload relation is only defined by the equality of their `id` – MrYoshiji Aug 07 '17 at 15:08
  • As @MrYoshiji mentions, withouth the `ids`, it will do a single query – MatayoshiMariano Aug 07 '17 at 15:11
0

What you need is INNER JOIN where joining condition must be the ids of the table

Upload.joins("INNER JOIN medias ON media.id = uploads.id")

This will be translated to: 

"SELECT `uploads`.* FROM `uploads` INNER JOIN media ON media.id = uploads.id"

Edit:

As MrYoshiji said in this comment, Upload.where(id: Media.all) also will do a single query.

So both options option will return all the upload records which ids exists in the media table

MatayoshiMariano
  • 2,026
  • 19
  • 23