Database Explorer – Backjoin through two tables

10 days ago

steelbull
Member | 180
+
0
-

Hi, I need to do a simple backjoin through two tables:

table1: projects_pdca
table2: projects
table3: teams

I have something like this:

$pdcaPlans = $this->context->table('project_plan_pdca')
           ->where('project_plan_pdca.type_id', 2)
           ->where('project:teams.user_id', $user_id);

I need to make:

SELECT * FROM project_plan_pdca
LEFT JOIN projects ON projects.id = project_plan_pdca
LEFT JOIN teams ON teams.project_id = project_id
WHERE project_plan_pdca.type_id = 2 AND teams.user_id = $user_id

Problem is, how can I make the second where clausule:

AND teams.user_id = $user_id

Last edited by steelbull (2019-02-06 11:07)

10 days ago

steelbull
Member | 180
+
0
-

For now, I was doying it by following way:

$query = "SELECT project_plan_pdca.* FROM project_plan_pdca" .
            " LEFT JOIN projects ON projects.id = project_plan_pdca.project_id" .
            " LEFT JOIN teams ON teams.project_id = projects.id" .
            " WHERE project_plan_pdca.type_id = ? AND teams.user_id = ?";
        $pdcaPlans = $this->context->query($query, 2, $user_id)
            ->fetchAll();

Last edited by steelbull (2019-02-06 19:45)

10 days ago

CZechBoY
Member | 3258
+
0
-

And what sql generates your command?

This seems ok at first sight.

Try this

$pdcaPlans = $this->context->table('project_plan_pdca')
           ->where('project_plan_pdca.type_id', 2)
           ->where('projects:teams.user_id', $user_id);

Last edited by CZechBoY (2019-02-06 14:01)

10 days ago

steelbull
Member | 180
+
0
-

CZechBoY wrote:

And what sql generates your command?

This seems ok at first sight.

Try this

$pdcaPlans = $this->context->table('project_plan_pdca')
           ->where('project_plan_pdca.type_id', 2)
           ->where('projects:teams.user_id', $user_id);

No, It not works. I dont know, how I can get the SQL, because getSQL(); not works because context will generate the exception. The exception message is:

No reference found for $project_plan_pdca->projects.

10 days ago

CZechBoY
Member | 3258
+
0
-

and exception for your query?

9 days ago

steelbull
Member | 180
+
0
-

@CZechBoY Its InvalidArgumentException: https://paste.pics/4PK0J

9 days ago

CZechBoY
Member | 3258
+
0
-

It is same as for my query.
Try it for your query.

$pdcaPlans = $this->context->table('project_plan_pdca')
           ->where('project_plan_pdca.type_id', 2)
           ->where('project:teams.user_id', $user_id);

9 days ago

steelbull
Member | 180
+
0
-

CZechBoY wrote:

It is same as for my query.
Try it for your query.

$pdcaPlans = $this->context->table('project_plan_pdca')
           ->where('project_plan_pdca.type_id', 2)
           ->where('project:teams.user_id', $user_id);

@CZechBoY I also tryied it, the exception was the same. Maybe Nette do not support backjoin throught two tables : – ( I replaced it with SQL query, but I want to know answer for the further use.

Last edited by steelbull (2019-02-07 14:45)

9 days ago

CZechBoY
Member | 3258
+
0
-

I use many backjoins, it is supported in some trivial way (like join through primary key).
I will try tomorrow.

7 days ago

Eda
Member | 212
+
+1
-

Hi.

Do you have properly defined foreign keys in the database from table project_plan_pdca and column project_id to table projects and column id?

It is necessary (unless you have defined your own custom database structure) for Database Explorer. Otherwise he doesn't know over which columns he is supposed to JOIN.

And I would also try this:

$pdcaPlans = $this->context->table('project_plan_pdca')
           ->where('project_plan_pdca.type_id', 2)
           ->where('project_id:teams.user_id', $user_id);

Last edited by Eda (2019-02-10 03:48)