8.5.2: Weird Doctrine query behavior
Permalink
I have a list of statuses. I want to show only those items, status of which corresponds to that with a value set to true.
For example, one of the statuses is called 'draft' and it has a value 'show_status' set to true. My items database has a column with the status reference. And I assign the status 'draft' to some items. Now I want to get only those items which correspond to a status with the 'show_status' set to true.
It all seems to work except for the fact I can't understand why because it only works with the opposite logic. Unless I don't get something...
Here's the code bit which filters the items:
I have a problem understanding this bit: "s.show_status != ?". I read it as it should show only those items where the show_status in NOT true. But it works as I want! If I change it to "s.show_status = ?" - it shows all items EXCEPT for those with that status.
Something's weird here... How can that be? The 'show_status' are indeed set to 1 in the database.
For example, one of the statuses is called 'draft' and it has a value 'show_status' set to true. My items database has a column with the status reference. And I assign the status 'draft' to some items. Now I want to get only those items which correspond to a status with the 'show_status' set to true.
It all seems to work except for the fact I can't understand why because it only works with the opposite logic. Unless I don't get something...
Here's the code bit which filters the items:
$this->status = true; ... if ($this->status) { $query->leftJoin('c', 'Statuses', 's', 's.status_id = c.status') ->andWhere('s.show_status != ?')->setParameter($paramcount++, '%'. $this->status. '%'); }
I have a problem understanding this bit: "s.show_status != ?". I read it as it should show only those items where the show_status in NOT true. But it works as I want! If I change it to "s.show_status = ?" - it shows all items EXCEPT for those with that status.
Something's weird here... How can that be? The 'show_status' are indeed set to 1 in the database.
Is it use of != instead of <> ?
Yes, I'm checking if s.show_status is true or not. It has to show items if it's true, while in fact it shows them when it's NOT true... or so I read it.
I'd write it in php like this:
I'd write it in php like this:
$this->status = true; if (show_status == $this->status) { ShowItems(); }
I'm specifically talking about the syntax using within the query, not the logic.
<> is the more traditional SQL style syntax.
My understanding is that != should be able to be used in place of <>, but older MySQL _might_ not support that. If it wasn't supported, I would be expecting an error, but still.
What happens if you do something like:
->andWhere('s.show_status <> 1');
(ignoring for a moment that you''re passing the status in as something that can change)
EDIT - And I know that it's Doctrine Query Syntax, not SQL
<> is the more traditional SQL style syntax.
My understanding is that != should be able to be used in place of <>, but older MySQL _might_ not support that. If it wasn't supported, I would be expecting an error, but still.
What happens if you do something like:
->andWhere('s.show_status <> 1');
(ignoring for a moment that you''re passing the status in as something that can change)
EDIT - And I know that it's Doctrine Query Syntax, not SQL
I've just tried it and it works exactly the same way as '!=' and THIS is weird. Because it should work ONLY when the two arguments ARE equal, i.e. ONLY when show_status == $this->status and NOT in any other case.
Is it is because the values in the database for that field are null and not 0?
No, all fields are either 0 or 1
I got it!
I couldn't notice I used wildcards with an equation (doh!):
it simply has to be:
I couldn't notice I used wildcards with an equation (doh!):
andWhere('s.show_status = ?')->setParameter($paramcount++, '%'. $this->status. '%');
it simply has to be:
andWhere('s.show_status = ?')->setParameter($paramcount++, $this->status);
