Join that even when no match exists still returns the first table row data
Join that even when no match exists still returns the first table row data
javismiles
Posts: 205Questions: 38Answers: 3
in Editor
I want to make a join in which,
$editor->leftJoin( 'workimp', 'workimp.workid', '=', 'worksol.workid' );
if the condition in the join happens then all proceeds normally,
and if the condition returns nothing then I still want to get my row back even if I get no info from that secondary table but I still want to get back the rest of the first table
so far I cannot do this, how can I do it? thank u
This discussion has been closed.
Answers
according to what I read
"MySQL, by default, performs an INNER JOIN, where only rows with a record in both tables are returned.
You can do a LEFT JOIN or RIGHT JOIN to tell MySQL to return one side of the join even if the other side is empty. In this situation, you'd want a RIGHT JOIN."
however in my case when no match happens Im getting no results!!!
So is datatables really doing a LEFT JOIN? in that case I Should be getting results back even when no match happens, but that's not happening
well yes according to datatables
"An SQL left join returns all rows from the left table (in the case of Editor, the table that the Editor class is initialised with, even if there are no matches in the right table (the table being joined on). If there is no data in the right table, null values will be used for the columns being read from that table."
then I dont understand why Im getting back only rows where a match happens between the tables of the leftjoin,
<?php ? ?>in my case Im initializing editor with the first table , the one I always want, and then left joining with that other table, and yet im only getting results when a match happens between both tables, I dont understand why, according to datatables, I should be getting results for all the rows
here is where for 1 of the fields I apply the condition to get the stuff from the other table
else if ($etype=="worksol" && $fields[$i]['i']=="worksol.spend"){
$editor->fields(
Field::inst( 'SUM(workimp.amount)', 'worksol.spend' ))
// ->set( false )
->where( function ($q) {
$q->where( 'workimp.type', '1', '=' );
});
If I eliminate this, then I get all the rows
is there something in that code that is making the left joins return only matches?
this is even more weird,
because when I do a left join with a mysql view
$editor->leftJoin( 'spend', 'worksol.workid', '=', 'spend.workid' );
then it all works perfect, even if only 1 row of the view matches, the query still returns all the rows of the first table, it works perfect!
however if I dont use a view, then it only returns the matches!
help please
seems that somehow, the process of specifying the SQL function plus the where conditions, which is the case of the view is internal to the view, but when I dont use a view I have to expose it here:
$editor->fields(
Field::inst( 'SUM(workimp.amount)', 'worksol.spend' ))
// ->set( false )
->where( function ($q) {
$q->where( 'workimp.type', '1', '=' );
});
it seems that such process somehow constrains the returned rows to only the matching ones, weird
I would suggest adding
->debug( true )to your Editor PHP code (just before the->process( ... )call). That will show the SQL statement that Editor is building and executing in the JSON returns from the server. What does it return please?Allan
thank u Allan, will add that and see what it returns and share it