[Adonis/Lucid/Orm] Problem with Relationship

[moDevsome]

Hello everybody,

First, Happy new year at all, I wish you good jobs and happy coding.

I've a very basic "Product -> Category" database structure.

TABLE products

col name	col type
id	int(11)
category_id	int(11)
name	varchar(180)
state	smallint(6)

TABLE categories

col name	col type
id	int(11)
name	varchar(180)
state	smallint(6)

A Product entity is associated with a Category entity through the foreign key : products.category_id

MODEL Product

export default class Product extends BaseModel {

  @column({ isPrimary: true })
  public id: number;

  @column()
  public name: string;

  @column()
  public state: number;

  @column()
  public categoryId: number;

  @belongsTo(() => Category)
  public Category: BelongsTo<typeof Category>;

}

MODEL Category

export default class Category extends BaseModel {
  @column({ isPrimary: true })
  public id: number;

  @column()
  public name: string;

  @column()
  public state: number;
}

The relation is well taken in consideration when I get the data with a simple SQL query, but the object returned by one of the folowing methods does not contain a Category object

• Product.find(1)
• Product.query()
• Product.query().preload('Category')

I've also tried differents decorators (@hasone, @belongsTo) with differents configuration in my Product model, but I could not achieve my goal.

What I want

I would like to get the data of the category associated with the product, like it's showed here in the User exmple : https://youtu.be/ftpqvs_9QN0?t=906

What I get

The query return only the product data.

Context

• adonisjs/core: 5.9.0,
• adonisjs/lucid: 18.4.2,
• adonisjs/repl: 3.1.11,
• adonisjs/session: 6.4.0,
• adonisjs/shield: 7.1.1,
• adonisjs/view: 6.2.0,
• luxon: 3.4.4,
• mysql2: 3.6.5,
• database : 11.2.2-MariaDB-1:11.2.2

??? I take this opportunity to ask you an another question : here's a way to get (in debug mode) the SQL query made by the framework ?

Thankz by advance for your help.

Mickaël

[kevin-tournie]

Hi, here is a repo where I tried to solve your issue : repo. I don't have exactly the same context but I think the key point here is the OneToMany relationship between Category and Product. I hope it will help you !

Debug mode for the database : adonis doc

Have a nice weekend :)

[kevin-tournie]

Maybe I can delete the OneToMany in fact ans keep the belongsTo. I'm gonna try this later !

[kevin-tournie]

Ok it works without the oneToMany field my bad !

[moDevsome]

Hello @kevin-tournie

Thank you very much for your work !
... and thank you for your link to the documentation, I could activate the database query log output.

For me, It's a "belongsTo" relation because 1 product belong to 1 category.
So, I'm not sure to understand you last answer... is it finaly work for you ?

I've tried new solutions on my side, inspired by what you done, but it definitily don't work like in the exemple showed in the video https://youtu.be/ftpqvs_9QN0?t=906
However, the category data are well presents in the $preload sub object...

I see that the builded query does not reproduce a "join" syntaxe.
I'm expecting something like this :
select p.id, p.name, c.name from products p left join categories c on c.id = p.category_id;
but Lucid build this query :
SELECT * FROM `products` WHERE `id` = ?
SELECT * FROM `categories` WHERE `id` IN (?)
So, 2 queries instead of 1.

Is there a solution for using a true join syntax ?

[kevin-tournie]

Hello @moDevsome !

Yes it works but I'm not sure if a real jointe is done by LucidORM. When I use
const product = await Product.findOrFail(id); await product.load("category");
I see only one query, which is weird because I don't understand how it can retrieve the category info...
This first method gives me the following response :
{ "id": 1, "name": "salé", "state": 0, "category_id": 1, "category": { "id": 1, "name": "salé", "state": 0 } }
The second method I found is to use the query builder with 'preload '
const product = await Product.query().where("id", id).preload("category");

I can clearly see 2 SQL queries in the console when I request a product and I get the same result like before.

The last method is to use the query builder and manually create the joint like this :
const product = await Database.query() .select( "p.id", "p.name as product_name", "p.state", "c.name as category_name" ) .from("products as p") .where("p.id", id) .leftJoin("categories as c", "c.id", "=", "p.category_id");
And we can see that a joint is done in the console : select p.id, p.nameasproduct_name, p.state, c.nameascategory_namefromproductsaspleft joincategoriesasconc.id=p.category_idwherep.id = ? [ '1' ]

In the response :
[ { "id": 1, "product_name": "salé", "state": 0, "category_name": "salé" } ]

I pushed my changes on the repo

[kevin-tournie]

Documentation : doc

[moDevsome]

I see only one query, which is weird because I don't understand how it can retrieve the category info...
Yes, I've seen this too.

The last method is to use the query builder and manually create the joint
Yes, that is the solution that I choose.

Thanks for your answer Kevin, I keep this discussion opened... maybe one day someone will know how to explain why I can't reproduce the example showed in the tutorial.

[kevin-tournie]

You're welcome. Yes, I'd like an explanation too !