CODEWITHSUNDEEP
sqlbookshelf.js
Christopher Avery
Christopher Avery

Reputation: 113

Multiple joins in bookshelf js

Im trying to make an api for a shop using bookshelf as my ORM. To make things restful I want my path to look something like this. '/mens/jackets' My data is structured so that to do this i have to find the category(male/female) so I can get its related section(jackets, jeans, hats ect.) then get the related products for that section. The data is such that there are 2 section rows with a name of jacket however one a has a fk linking the category to 1(make) and the other linking to 2(female).

I have found this in the documentation but it wont work as (I'm guessing) you can only chain related when the first related returns a single entity and in this case my categories have many sections.

new Photo({id: 1}).fetch({
   withRelated: ['account']
  }).then(function(photo) {
   if (photo) {
  var account = photo.related('account');
  if (account.id) {
   return account.related('trips').fetch();
  }
 }
  });

In raw sql my query would look like this 

SELECT * FROM sections
JOIN categories ON categories.id = sections.category_id
JOIN products ON products.section_id = sections.id
where categories.name = 'mens' AND sections.name = 'jackets';

Any help would be greatly appreciated.

Upvotes: 0

Views: 1042

Answers (2)

Grisha Ghukasyan
Grisha Ghukasyan

Reputation: 100

You can directly fetch trips with account, like :

new Photo({id: 1}).fetch({
  withRelated: [
   { 'account': qb => qb.where(needed conditions) },
   'account.trips',
  ]
}).then(function(photo) {
    YOUR CODE
});

Now you juste have to check if .related('account') and .related('account').related('trips') are not null before use them

I hope it helps :)

Upvotes: 1

Rachel613
Rachel613

Reputation: 56

After creating a Sections model you can use a query like this one.

Sections.query(qb => {
qb.leftJoin('categories', 'sections.category_id', 'categories.id);
qb.leftJoin('products', 'sections.id', 'products.section_id);
qb.where('categories.name', 'men');
qb.andWhere('sections.name', 'jackets');
}.fetchOne({withRelated: 'products'});

Upvotes: 0

Related Questions