Unable to find multiple records that have a common relationship


#1

I’m building a web page where a “related articles” block shows up at the end of the page.
I would like to make a query to find a few articles that shares the same tag. It’s might be important to note that my tags / articles relationship is a many to many.

In my example, the tag’s id is 2, and I did the following:

const posts = Post
        .query()
        .with('tags', (builder) => {
          builder.where('id', 2)
        })
        .fetch()

Even if there is no error, I get no result at all. I should get at least a dozen results.

What am I doing wrong?

Thank you in advance


#2

I suggest to get into habit of debugging database queries. https://adonisjs.com/docs/4.1/database#_debugging

And then share the query being executed


#3

Hello, this is the output from setting “debug” to “true” in my database.js file (I use SQLite)

{ method: 'first',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ 1 ],
  __knexQueryUid: '30450602-5621-45d4-890f-6132eb12dbe3',
  sql: 'select * from `users` order by `id` asc limit ?' }
{ method: 'first',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ 'ultimate-test', 1 ],
  __knexQueryUid: '8b190350-2e6f-4123-a519-9c4f3ac24c05',
  sql: 'select * from `articles` where `slug` = ? limit ?' }
{ method: 'first',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ 1 ],
  __knexQueryUid: '319a9e07-4d97-4559-bf35-d631c7bc20b1',
  sql: 'select * from `settings` order by `id` asc limit ?' }
{ method: 'select',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ 25 ],
  __knexQueryUid: '9610dba1-1525-4688-acd4-f716bf249990',
  sql:
   'select `tags`.*, `article_tag`.`tag_id` as `pivot_tag_id`, `article_tag`.`article_id` as `pivot_article_id` from `tags` inner join `article_tag` on `tags`.`id` = `article_tag`.`tag_id` where `article_tag`.`project_id` in (?)' }
{ method: 'select',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [],
  __knexQueryUid: '0d285670-8445-4130-97ac-18b62382f02b',
  sql: 'select * from `articles`' }
{ method: 'select',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ 2, 13, 14, 15, 16, 17, 18, 19, 25 ],
  __knexQueryUid: '53dcd36b-bc48-4b02-8905-0a53f63c7dd2',
  sql:
   'select `tags`.*, `article_tag`.`tag_id` as `pivot_tag_id`, `article_tag`.`article_id` as `pivot_article_id` from `tags` inner join `article_tag` on `tags`.`id` = `article_tag`.`tag_id` where `id` = ? and `article_tag`.`article_id` in (?, ?, ?, ?, ?, ?, ?, ?)' }

By the way, since I use WebStorm for the development, I tried to run the SQL, it gave me some results, but not the ones I expected (like, an array of articles)


#4

I managed to overcome this strange issue by, instead of requesting articles with the same tag, requesting all the articles that are linked to this tag.

const tag = await Tag.find(2) // The current article (will be dynamic)
      const articles = await tag
        .article()
        .whereNot('id', 25) // the current article (will also be dynamic)
        .fetch()

      return response.status(200).json(articles)