Translate a MySQL query into Knex QueryBuilder syntax equivalent?

Question

I'm having a hard time replicating a specific MySQL query into Knex (I am using Bookshelf as the ORM). The SQL query itself returns the correct dataset when querying the database directly in the MySQL terminal.

MySQL Query:

SELECT processed_articles.*, publications.pub_name
FROM processed_articles
INNER JOIN processed_articles_trends ON processed_articles_trends.processed_article_id = processed_articles.id
INNER JOIN publications ON publications.id = processed_articles.pub_id
WHERE (processed_articles_trends.trend_id = 2);

Knex QueryBuilder:

knex.select(['publications.pub_name', 'processed_articles.*'])
  .innerJoin('processed_articles_trends', 'processed_articles.id', 'processed_articles_trends.processed_article_id')
  .innerJoin('publications', 'processed_articles.pub_id', 'publications.id')
  .where('processed_articles_trends.trend_id', '=', 2);

I know the Knex syntax is incorrect, but I'm not sure where (I suspect the array syntax in line 1 of the Knex query...) & cannot for the life of me figure out how else to translate the SQL. The table 'processed_articles_trends' represents a join table between the table 'processed_articles' and the 'trends' table.

I initially tried using my Bookshelf model definitions and the 'withRelated' parameter, but Bookshelf was performing some voodoo under the hood that made it difficult to for me establish how to pull back the field I needed from the 'publications' table.

Grateful for any insights - many thanks.

Answer 1

knex('processed_articles')
  .innerJoin('processed_articles_trends', 'processed_articles.id', 'processed_articles_trends.processed_article_id')
  .innerJoin('publications', 'processed_articles.pub_id', 'publications.id')
  .where('processed_articles_trends.trend_id', '=', 2)
  .select(db.raw('processed_articles.*'))
  .select('publications.pub_name')