Database Explorer – Backjoin through two tables

steelbull

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)

steelbull

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)

CZechBoY

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)

steelbull

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.

CZechBoY

and exception for your query?

steelbull

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

CZechBoY

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);

steelbull

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)

CZechBoY

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

Eda

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)