primary key and foreign key is same after join

[shakhinn]

code of query

auto headings = DatasetType::whereEq("name", "2d")->first()
                        ->headings()
                        ->join("heading_names", "heading_names.id", "=", "headings.heading_name_id")
                        .with("name")
                        .orderBy("heading_names.name")
                        .get();

sql:

select * from "headings" inner join "heading_names" on "heading_names"."id" = "headings"."heading_name_id" where "headings"."dataset_type_id" = 1 and "headings"."dataset_type_id" is not null and "headings"."deleted_at" is null

then i write

        for (auto& h: headings) {
            qDebug() << h.getRelation<HeadingName, Orm::One>("name")->name();
            qDebug() << h.getAttribute("heading_name_id");
            qDebug() << h.getAttribute("id");
               }

result is QVariant(qulonglong, 7) QVariant(qulonglong, 7)

but when i execute SQL query result is

ids should not be equal.

[silverqx]

Your query is really weird, what headings() returns?

[silverqx]

Also, you are mixing with() with join()? But let's focus on that headings() for now.

[shakhinn]

headings() returns std::unique_ptr<HasMany<DatasetType, Heading>>

[shakhinn]

Also, you are mixing with() with join()? But let's focus on that headings() for now.

yeah. I need to sort Heading with dataset_type_id = 1, by name. Names are in other table. Аnd when I iterate through the array I want to see the names

I don't know how write correct code for this task

[silverqx]

Ok, when headings() method returns HasMany relation then it should be correct. 👍

I need to know how the name relation is defined as you are calling with("name"). I need to investigate this query to see if there is any bug in our code.

[shakhinn]

class Heading final : public Model< ... > {
...
std::unique_ptr<BelongsTo<Heading, HeadingName>> name() {
            return belongsTo<HeadingName>("heading_name_id", "id", QString::fromUtf8(__func__));
        }
}

class HeadingName final : public Model<HeadingName, Heading> {public SoftDeletes<HeadingName> 

        std::unique_ptr<HasMany<HeadingName, Heading>> headings() {
            return hasMany<Heading>("heading_name_id", "id");
        }

I understood from the documentation that the foreign key is specified first, and then the primary key. Maybe i shouldn't write "id" key in relation

[shakhinn]

as you are calling with("name").

i remove with from query. The behavior remained the same. The IDs are being rewritten

auto headings = DatasetType::whereEq("name", "2d")->first()
                  >headings()
                  ->join("heading_names", "heading_names.id", "=", "headings.heading_name_id")
                  .orderBy("heading_names.name")
                  .get();
for (auto& h: headings) {
    qDebug() << h.getAttribute("id");
    Debug() << h.getAttribute("heading_name_id");
}

[silverqx]

I understood from the documentation that the foreign key is specified first, and then the primary key

That's correct.

Maybe i shouldn't write "id" key in relation

There is nothing wrong if you specify it explicitly.

I test it when I will have a little time, I can't tell right away where the problem is, I need to model an entire case and reproduce it, and it will take some time. I think I have all the info I will need.

When I fastly look at the generated SQL query then it looks correct, it looks exactly how it should look like, but maybe I'm wrong.

Also, I would recommend to skip ORM and call the querybuilder directly something like this:

auto headings = DB::table("headings")
        ->leftJoin("heading_names", "heading_names.id", "=", "headings.heading_name_id")
        .whereIn("headings.id", {1, 2, 3})
        .orderBy("heading_names.name")
        .get();

Or use that whereEq("name", "2d"), try to play with these.

[shakhinn]

Thank you

[silverqx]

I have tested leftJoin() on HasMany relation and it works, I have written test for it here, so it works as expected for me. It's practically the same scenario as you are describing.

Did you find out how to do it with ORM mappings? Do you need help with it?

[shakhinn]

I looked at your solution and noticed that i wrote columns in join in the wrong order. I changed the order and everything worked. Thanks for the help!