Custom SQL queries in Directus

Question

Let's say I have this kind of MySQL database structure :

Menu :

id  name
 3  Tartiflette
 4  Lasagne

Ingredient :

id  name        price
15  Carrot      2.00    
39  Garlic      3.00    
40  Reblochon   5.00    
55  Onion       1.00

Menu_ingredient :

id  ingredient_id   menu_id quantity
4   15              4       2
5   55              4       1
6   39              4       1
7   40              3       2
8   55              3       3

Resa_menu :

id  resa_id menu_id people
 1  1       4       3
 2  1       3       2

Resa :

id
1

How could I get all the ingredients from one reservation, using one query parameter with the items/ endpoint ?

I tried the query using items/resa_menu?fields=*.* (and some other combinations, but out of luck).

I also set up an SQL View (virtual table) getting all info needed with a query, but this kind of SQL doesn't seem to be fetched by Directus.

SELECT 
  ingredient.name as ing_name,
  (resa_menu.people * ingredient.price) as price,
  (resa_menu.people * menu_ingredient.quantity) as quantity 
FROM resa_menu 
INNER JOIN menu             ON menu.id = resa_menu.menu_id 
INNER JOIN resa             ON resa.id = resa_menu.resa_id 
INNER JOIN menu_ingredient  ON menu_ingredient.menu_id = menu.id 
INNER JOIN ingredient       ON ingredient.id = menu_ingredient.ingredient_id 
GROUP BY ingredient.id;

Expected output :

ing_name       price  quantity
Carrot         6.00   6
Garlic         9.00   3
Reblochon      10.00  4
Onion          3.00   3

The specific goal here is to get a summary of all ingredients needed for the reservation (combining all menus, multiplying quantity by number of people). But my question is more generic on : how can you do multiple JOINs like in this query with Directus API ?

Many thanks for the help!

I will... but basically I would just need to do a query with INNER JOIN on all of those tables, like : SELECT resa_menu.id, menu.name, ingredient.name, (resa_menu.people * ingredient.price) as price, (resa_menu.people * menu_ingredient.quantity) as quantity FROM resa_menu INNER JOIN menu ON menu.id = resa_menu.menu_id INNER JOIN resa ON resa.id = resa_menu.resa_id INNER JOIN menu_ingredient ON menu_ingredient.menu_id = menu.id INNER JOIN ingredient ON ingredient.id = menu_ingredient.ingredient_id WHERE resa_menu.id = 1 GROUP BY ingredient.id — Romain
– Romain, Commented Feb 24, 2019 at 6:26
@Romin post it on your question not in comment and shample data and expected output will help to get faster solution in SO — Zaynul Abadin Tuhin
– Zaynul Abadin Tuhin, Commented Feb 24, 2019 at 6:35
@zay Thanks for the tip, I updated my question with specific details. — Romain
– Romain, Commented Feb 24, 2019 at 7:13

Shea Lavington · Accepted Answer · 2019-02-24 10:29:58Z

3

I think what you mean is you're requesting the endpoint /items/Resa_menu and doing *.* is returning Menu_ingredient related data, however you would like a list of ingredients returned too? for this, would you not just change your fields query to *.*.* to get three levels of relations?

Or is it that the ?fields=*.* simply isn't working at all?

answered Feb 24, 2019 at 10:29

Shea Lavington

4952 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rijk Over a year ago

Also make sure you have the relational fields setup! The relationships won't work if they haven't been setup

Romain Over a year ago

@Rijk thank you, that was exactly my problem. I think this would need to be added to the doc as this is not obvious? Thank you also Shea.

Zaynul Abadin Tuhin · Accepted Answer · 2019-02-24 07:52:02Z

0

use left join and you does not need group by

          select i.name,(i.price* coalesce(rm.people,1)) as price,
             (mi.quantity*coalesce(rm.people,1)) as quantity
            from Ingredient i left join 
            Menu_ingredient mi on i.id =mi.ingredient_id
            left join Resa_menu rm on mi.menu_id =rm.menu_id

edited Feb 24, 2019 at 7:52

answered Feb 24, 2019 at 7:34

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

2 Comments

Romain Over a year ago

Thanks for your reply and for your time @Zaynul. My question was more on how to reproduce this SQL query on Directus API endpoints.

Zaynul Abadin Tuhin Over a year ago

@Romain does not this query provide your expected result that you mentioned in question

Collectives™ on Stack Overflow

Custom SQL queries in Directus

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related