Skip to content

Loading…

DDC-479: MEMBER OF generates wrong SQL #4982

Closed
doctrinebot opened this Issue · 12 comments

1 participant

@doctrinebot

Jira issue originally created by user @beberlei:

        $dql = 'SELECT p FROM Whitewashing\Blog\Post p WHERE ?1 MEMBER OF p.tags';

        return $this->_em->createQuery($dql)
                         ->setParameter(1, $tagId)
                         ->setMaxResults(10)
                         ->getResult();
SELECT b0*.post_headline AS post_headline0, b0_.post_text AS post_text1, b0_.post_created AS post_created2, b0_.is_published AS is_published3, b0_.id AS id4, b0_.author_id AS author_id5, b0_.blog_id AS blog_id6 FROM blog_posts b0_ WHERE EXISTS (SELECT 1 FROM blog_posts_tags b1_ INNER JOIN blog_tags b2_ ON b1_.post_id = b0_.id WHERE b1_.tag_id = b2_.id AND b2*.id = 1);

Generates the following error: ERROR 1054 (42S22): Unknown column 'b0_.id' in 'on clause'

@doctrinebot

Comment created by romanb:

Correct SQL should be:

SELECT b0*.post_headline AS post_headline0, b0_.post_text AS post_text1, b0_.post_created AS post_created2, b0_.is_published AS is_published3, b0_.id AS id4, b0_.author_id AS author_id5, b0_.blog_id AS blog_id6 FROM blog_posts b0_ WHERE EXISTS (SELECT 1 FROM blog_posts_tags b1_ INNER JOIN blog_tags b2_ ON b1_.tag_id = b2_.id WHERE b1_.post_id = b0_.id AND b2*.id = 1)
@doctrinebot

Comment created by romanb:

Should be fixed now.

@doctrinebot

Issue was closed with resolution "Fixed"

@doctrinebot

Comment created by jorritposthuma:

Not sure if it is the same, but MEMBER OF might still not generating correct SQL (or we are doing something wrong):

SELECT g FROM Model:User\Group g WHERE :user MEMBER OF g.users

Tables:
User_User

/****
 * @var array The groups of this user
 * @method array getGroups() Returns all the groups
 * @method void setGroups($groups) Sets the groups
 * @ManyToMany( targetEntity = "JJ\Model\Instance\User\Group", inversedBy = "users" )
 * @JoinTable( name="User_UserGroups",
 *          joinColumns         = { @JoinColumn( name = "user_id",  referencedColumnName = "id" ) },
 *          inverseJoinColumns  = { @JoinColumn( name = "group_id", referencedColumnName = "id" ) }
 *      )
 */
protected $groups;

User_Group

/****
 * @var array The users of this group
 * @method array getUsers() Returns all the users
 * @method void setUsers($users) Sets the users
 * @ManyToMany( targetEntity = "JJ\Model\Instance\User\User", mappedBy = "groups" )
 * @JoinTable( name="`User_UserGroups`",
 *          joinColumns         = { @JoinColumn( name = "group_id", referencedColumnName = "id" ) },
 *          inverseJoinColumns  = { @JoinColumn( name = "user_id",  referencedColumnName = "id" ) }
 *      )
 */
protected $users;

Results in:
SELECT u0.id AS id0, u0_.name AS name1, u0_.removable AS removable2 FROM User_Group u0_ WHERE EXISTS (SELECT 1 FROM u1_ INNER JOIN User_User u2_ ON u1_.user_id = u2_.id WHERE u1_.group_id = u0_.id AND u2.id = ?)

Should be:
SELECT u0.id AS id0, u0_.name AS name1, u0_.removable AS removable2 FROM User_Group u0_ WHERE EXISTS (SELECT 1 FROM User_UserGroups u1_ INNER JOIN User_User u2_ ON u1_.user_id = u2_.id WHERE u1_.group_id = u0_.id AND u2.id = 1)

@doctrinebot

Comment created by jorritposthuma:

I guess that _validateAndCompleteMapping of AssociationMapping needs the addition of:

$this->joinTable = $mapping['joinTable']; at line 222 resulting in:

/****
 * Validates & completes the mapping. Mapping defaults are applied here.
 *
 * @param array $mapping
 * @throws MappingException If something is wrong with the mapping.
 */
protected function _validateAndCompleteMapping(array $mapping)
{        
    // Mandatory attributes for both sides
    if ( ! isset($mapping['fieldName'])) {
        throw MappingException::missingFieldName();
    }
    $this->sourceFieldName = $mapping['fieldName'];

    if ( ! isset($mapping['sourceEntity'])) {
        throw MappingException::missingSourceEntity($mapping['fieldName']);
    }
    $this->sourceEntityName = $mapping['sourceEntity'];

    if ( ! isset($mapping['targetEntity'])) {
        throw MappingException::missingTargetEntity($mapping['fieldName']);
    }
    $this->targetEntityName = $mapping['targetEntity'];

    // Mandatory and optional attributes for either side
    if ( ! isset($mapping['mappedBy'])) {            
        // Optional
        if (isset($mapping['joinTable']) && $mapping['joinTable']) {
            if ($mapping['joinTable']['name'][0] == '`') {
                $mapping['joinTable']['name'] = trim($mapping['joinTable']['name'], '`');
                $mapping['joinTable']['quoted'] = true;
            }
            $this->joinTable = $mapping['joinTable'];
        }
        if (isset($mapping['inversedBy'])) {
            $this->inversedBy = $mapping['inversedBy'];
        }
    } else {
        $this->isOwningSide = false;
        $this->mappedBy = $mapping['mappedBy'];
        $this->joinTable = $mapping['joinTable'];
    }

    // Optional attributes for both sides
    $this->fetchMode = isset($mapping['fetch']) ? $mapping['fetch'] : self::FETCH_LAZY;
    $cascades = isset($mapping['cascade']) ? $mapping['cascade'] : array();

    if (in_array('all', $cascades)) {
        $cascades = array(
           'remove',
           'persist',
           'refresh',
           'merge',
           'detach'
        );
    }

    $this->isCascadeRemove = in_array('remove',  $cascades);
    $this->isCascadePersist = in_array('persist', $cascades);
    $this->isCascadeRefresh = in_array('refresh', $cascades);
    $this->isCascadeMerge = in_array('merge',   $cascades);
    $this->isCascadeDetach = in_array('detach',  $cascades);
}
@doctrinebot

Comment created by romanb:

No, only the owning side has the join table. Your mapping is wrong. Remove @JoinTable from the inverse side entirely.

@doctrinebot

Comment created by jorritposthuma:

Thanks, just figured that out :).

@doctrinebot

Comment created by jorritposthuma:

Now i remember why we do have the @JoinTable on the inverse side. We want to specify a @JoinTable( name="User_UserGroups" ). It doen't work only specified on the owning side, and we figured out that this was the only way?

@doctrinebot

Comment created by jorritposthuma:

Never mind. Need to get some sleep. Sorry for the inconvenience. Only wanted to help. Love the Doctrine 2 project!!!

@doctrinebot

Comment created by jorritposthuma:

Sorry, last question, shouldn't the SqlWalker in that case call getQuotedJoinTableName on the owningAssoc, instead of assoc? ( Line 1404 rev. 0c07b31 )

@doctrinebot

Comment created by jorritposthuma:

Should i create a new Issue for that?

@doctrinebot

Comment created by @beberlei:

yes please!

@doctrinebot doctrinebot added this to the 2.0-BETA1 milestone
@doctrinebot doctrinebot closed this
@doctrinebot doctrinebot added the Bug label
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.