Relational icon indicating copy to clipboard operation
Relational copied to clipboard

Bug when conditions are applied to more than one table

Open vinyanalista opened this issue 11 years ago • 4 comments

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.

vinyanalista avatar Nov 18 '13 11:11 vinyanalista

I was able to do the query I wanted using:

$aContent = $mapper->content(array('id !=' => 60, 'category_id' => 30))->category->fetchAll();

vinyanalista avatar Nov 18 '13 11:11 vinyanalista

@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

nickl- avatar Nov 22 '13 08:11 nickl-

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.

alganet avatar Dec 03 '13 00:12 alganet

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

mrsoto avatar Dec 31 '15 00:12 mrsoto