Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Bug when conditions are applied to more than one table #47

Open
vinyanalista opened this issue Nov 18, 2013 · 4 comments
Open

Bug when conditions are applied to more than one table #47

vinyanalista opened this issue Nov 18, 2013 · 4 comments

Comments

@vinyanalista
Copy link

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
Copy link
Author

I was able to do the query I wanted using:

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

@nickl-
Copy link
Member

nickl- commented Nov 22, 2013

@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

@alganet
Copy link
Member

alganet commented Dec 3, 2013

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.

@mrsoto
Copy link

mrsoto commented Dec 31, 2015

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

4 participants