douche3244
2013-02-19 02:27
浏览 47
已采纳

将两个简单的SQL查询转换为Doctrine

First of all, I want to apologize for the length of this question; I didn't know how to properly ask my question without a lot of background. Please bear with me.

I'm converting a simple application that I use to hone my skills from my own custom database access schema to Doctrine. I chose Doctrine for a number of reasons, not the least of which is that I use it at my day job regularly. I also like how Doctrine is generally a pretty thin (appearing) layer that stays out of the way while still adding a lot of features.

I've converted the data access layer for the users table in my database to Doctrine. It's very unremarkable (simply getters and setters), except for a few fine details:

  • I need to have a custom repository for some specific queries and
  • the User object has a default ArrayCollection instantiated in the constructor

namespace model\entities;

/**
 * @Entity(repositoryClass="modelepositories\UserRepository")
 * @Table(name="users")
 */
class User{
    /* snip variables */

    /**
     * @OneToOne(targetEntity="Authentication", mappedBy="user", cascade="persist")
     */
    private $authentication;

    /**
     * @OneToMany(targetEntity="Contact", mappedBy="user", cascade="persist")
     */
    private $contacts;

    public function __construct() {
        $this->contacts = new \Doctrine\Common\Collections\ArrayCollection();
    }

    /* snip getters and setters */  
}

In my old schema, I had two custom queries that selected a subset of the users table.

They are:

public function search( $term = null ){
    if( !$term ){
        $sql = "SELECT *
                FROM
                " . $this->tableName . "
                ORDER BY
                    lname ASC,
                    fname ASC";
        $res = $this->db->q($sql);
    }
    else{
        $sql = "SELECT *
                FROM
                " . $this->tableName . "
                WHERE
                    lname LIKE ?
                    OR fname LIKE ?
                    OR CONCAT(fname, ' ', lname) LIKE ?
                ORDER BY
                    lname ASC,
                    fname ASC";
        $values = array( '%' . $term . '%', '%' . $term . '%', '%' . $term . '%' );
        $res = $this->db->qwv( $sql, $values );
    }

    return $this->wrap( $res );
}

and:

public function getAllWithRestrictions(){
    $sql = "SELECT *
            FROM
            " . $this->tableName . "
            WHERE
                userid IN
                (
                    SELECT
                        userid
                    FROM
                        " . $this->uiPre . "authentications
                    WHERE
                        resetPassword = 1
                        OR disabled = 1
                )";
    $res = $this->db->q( $sql );

    return $this->wrap($res);
}

where $this->db is a thin PHP PDO wrapper and $this->wrap does magic with zero/single/multiple rows returned and converting them into data objects.

Now, I figured this would be very easy to convert to Doctrine. In the case of getAllWithRestrictions it's simply a ->where, ->orWhere set, right? I don't know anymore.

I found these Stackoverflow questions that I used to try to construct my queries, but I'm running into error after error, and I'm not sure how far down the rabbit hole I need to go:

SQL Multiple sorting and grouping
Order by multiple columns with Doctrine
Doctrine: Multiple (whereIn OR whereIn) query?
Doctrine - or where?

My custom repository currently looks like this, but I can't say it's even close to correct as I've been fiddling with it for a long time, and it's just a hodge-podge of what I thought might work:

<?php
namespace modelepositories;
use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query\Expr;

class UserRepository extends EntityRepository{
    public function search( $term ){
        if( !$term ){
            return $this
                    ->_em
                    ->createQuery('SELECT u FROM model\entities\User u')
                    ->addOrderBy( 'u.lname' )
                    ->addOrderBy( 'u.fname' )
                    ->getResult();
        }
        else{
            $qb     = $this->_em->createQueryBuilder();

            $qb ->select(array('u'))
                ->from('model\entities\User', 'u')
                ->where( $qb->expr()->like( 'u.lname', '?1' ) )
                ->orWhere( $qb->expr()->like( 'u.fname', '?2' ) )
                ->orWhere( $qb->expr()->like( 'CONCAT(u.fname, \' \', u.lname)', '?3' ) )
                ->addOrderBy( 'u.lname' )
                ->addOrderBy( 'u.fname' )
                ->setParameters(
                    array(
                        1 => $term,
                        2 => $term,
                        3 => $term
                    )
                );

            $query = $qb->getQuery();
            return $query->getResult();
        }
    }

    public function getAllWithRestrictions(){
        $qb     = $this->_em->createQueryBuilder();

        $qb ->select(array('u'))
            ->from('model\entities\User', 'u')
            ->add('where', $qb->expr()->orx(
               $qb->expr()->eq('u.disabled', '1'),
               $qb->expr()->eq('u.resetPassword', '1')
           ));

        $query = $qb->getQuery();
        return $query->getResult();
    }
} 

EDIT: I just realized that I'm doing the getAllWithRestrictions query on the wrong table (it should be authentications.) In any case, it's the search method causing my issues right now. However, I will also need to know how to do something like $qb->expr()->eq('u.Authentication.disabled = '1') and I have no idea how DQL really works.

The particular error I'm getting right now is

  • Fatal error: Uncaught exception 'Doctrine\ORM\Query\QueryException' with message 'SELECT u FROM model\entities\User u WHERE u.lname LIKE ?1 OR u.fname LIKE ?2 OR CONCAT(u.fname, ' ', u.lname) LIKE ?3 ORDER BY u.lname ASC, u.fname ASC'

followed by

  • Doctrine\ORM\Query\QueryException: [Syntax Error] line 0, col 99: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got ','

But I've had a slew of different issues depending on how I construct the DQL/Query Builder.

Again, I expected these two SQL queries to be simple to convert to Doctrine / DQL. I'd like to do this without resorting to raw SQL through Doctrine (as many of the accepted answers I linked suggest). This MUST be easy. Does anyone know how to construct elegant Doctrine queries?

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • drwjv28028 2013-02-19 03:53
    已采纳

    According to the CONCAT function parser in doctrine, it only takes 2 parameters separated by a comma,

    //Doctrine/ORM/Query/AST/Functions/ConcatFunction.php
    $parser->match(Lexer::T_IDENTIFIER);
    $parser->match(Lexer::T_OPEN_PARENTHESIS);
    
    $this->firstStringPrimary = $parser->StringPrimary();
    $parser->match(Lexer::T_COMMA);
    $this->secondStringPrimary = $parser->StringPrimary();
    
    $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    

    So this

    ->orWhere( $qb->expr()->like( 'CONCAT(u.fname, \' \', u.lname)', '?3' ) )
    

    should be

    ->orWhere( $qb->expr()->like( 'CONCAT(u.fname, u.lname)', '?3' ) )
    

    This will not throw any errors, but may be not what you actually need. In that case, you will have to roll your own custom function for CONCAT :)

    打赏 评论
  • dongque5529 2013-02-20 01:21

    In addition to @Broncha's answer above (which helped solve the error by adding a nested CONCAT like 'CONCAT(u.fname, CONCAT(\' \', u.lname))'), the other function can be fixed by joining on the entity required, then adding WHERE clauses on the join like so:

    $qb ->select(array('u'))
        ->from('model\entities\User', 'u')
        ->leftJoin('u.authentication', 'a')
        ->add('where', $qb->expr()->orx(
            $qb->expr()->eq('a.disabled', '1'),
            $qb->expr()->eq('a.resetPassword', '1')
        ));
    
    打赏 评论

相关推荐 更多相似问题