Relational
Relational copied to clipboard
Bug when conditions are applied to more than one table
Hello all of you who develop Respect. First, let me thank you and say you're doing a really good job! I'm using Respect in a project of mine and today I was not able to perform a query based on the examples in your documentation.
The project I'm working on is a CMS, and I want to fetch all the contents that belongs to the category of ID equal to 30, which ID is different from 60. According to your documentation, I thought of this command:
$aContent = $mapper->content(array('id !=' => 60))->category[30]->fetchAll();
It was not bringing me the contents I expected, so I dove into the code and inserted an echo where you generate the query to see what the framework was looking for (release 0.5.1, file Mapper.php, line 223):
protected function createStatement(
Collection $collection, $withExtra = null
) {
$query = $this->generateQuery($collection);
if ($withExtra instanceof Sql) {
$query->appendQuery($withExtra);
}
echo $query; exit;
The generated query was:
SELECT content.*, category.* FROM content INNER JOIN category ON content.category_id = category.id WHERE category.id = ?
I decided to replace my command by your example:
<?php $mapper->comment(array("created_at >"=>strtotime('today')))
->post(array("created_at >"=>strtotime('7 days ago')))
->author[7]
->fetchAll();
And the generated query was actually:
SELECT comment.*, post.*, author.* FROM comment INNER JOIN post ON comment.post_id = post.id INNER JOIN author ON post.author_id = author.id WHERE author.id = ?
It seems it's only taking into account the last condition from the join.
I was able to do the query I wanted using:
$aContent = $mapper->content(array('id !=' => 60, 'category_id' => 30))->category->fetchAll();
@vinyanalista Thank you for the feedback.
This used to work can you please confirm which version you are using.
Also could you check if the query is generated correctly when omitting the filter on content:
$mapper->content->category[7]->fetch();
Should produce something like:
SELECT content.*
FROM content
INNER JOIN category ON content.category_id = category.id
WHERE category.id = 7
Try this:
<?php
namespace MyAPp;
class Mapper extends \Respect\Relational\Mapper
{
// TODO: Move this fix to Respect\Relational\Mapper
protected function buildTables(\Respect\Relational\Sql $sql, $collections)
{
$conditions = $aliases = $where = array();
foreach ($collections as $alias => $collection) {
$this->parseCollection(
$sql, $collection, $alias, $aliases, $conditions
);
$where = array_merge($conditions, $where);
}
return $sql->where($where);
}
}
I'm trying out this solution for a similar problem and I believe it could help. Use this extended class instead of the original Mapper. The fix changes how conditions for multiple tables are merged together (and hopefully fixes a bug). I didn't have the time to write appropriate test cases and fix it in the source, but I believe this could help.
I've a similar problem:
This statement:
$current = $mapper
->assigment[$id]
->classroom(array('id' => $classroom_id, 'usercustomer_id' => $usercustomer_id))
->fetch();
produce:
espect\Relational\Sql object {
query => (string) SELECT assigment.*, classroom.* FROM assigment INNER JOIN classroom ON assigment.classroom_id = classroom.id WHERE classroom.id = ? AND classroom.usercustomer_id = ?
params => array(2) (
[0] => (string) 2
[1] => (string) 3
)
}