Asked  7 Months ago    Answers:  5   Viewed   36 times

I will go insane with this minimal error that I'm not getting fix. I want to select entries between two days, the examples below ilustrate all my fails:

opt 1.

$qb->where('e.fecha > ' . $monday->format('Y-m-d'));
$qb->andWhere('e.fecha < ' . $sunday->format('Y-m-d'));

result (0 entries):

SELECT r0_.id_reservacion AS id_reservacion0, r0_.fecha AS fecha1, r0_.cliente AS cliente2 
FROM reservacion r0_ 
WHERE (r0_.fecha > 2012 - 07 - 16) AND (r0_.fecha < 2012 - 07 - 22)

opt 2

$qb->add('where', 'e.fecha between 2012-01-01 and 2012-10-10');

result (0 entries):

SELECT r0_.id_reservacion AS id_reservacion0, r0_.fecha AS fecha1, r0_.cliente AS cliente2 
FROM reservacion r0_ WHERE r0_.fecha 
BETWEEN 2012 - 01 - 01 AND 2012 - 10 - 10

This is my table with current entries:

id      fecha            cliente
1   2012-07-16 00:00:00    2    
2   2012-07-16 13:00:00    4    
3   2012-07-22 23:00:00    4

Edit 1

In order to evaluate the sql to avoid doubts, I ran this query:

$qb->where('e.fecha > ' . $sunday->format('Y-m-d'));

result (3 entries):

SELECT r0_.id_reservacion AS id_reservacion0, r0_.fecha AS fecha1, r0_.cliente AS cliente2 

So, looks like the sql is not the problem. FROM reservacion r0_ WHERE r0_.fecha > 2012 - 07

 Answers

82

You can do either…

$qb->where('e.fecha BETWEEN :monday AND :sunday')
   ->setParameter('monday', $monday->format('Y-m-d'))
   ->setParameter('sunday', $sunday->format('Y-m-d'));

or…

$qb->where('e.fecha > :monday')
   ->andWhere('e.fecha < :sunday')
   ->setParameter('monday', $monday->format('Y-m-d'))
   ->setParameter('sunday', $sunday->format('Y-m-d'));
Wednesday, March 31, 2021
 
conmen
answered 7 Months ago
12

There are two kinds of join queries in Doctrine2:

1) Regular joins
2) Fetch joins

Check the documentation chapter 14.2.2. Joins for more details.

So if you want to fetch join vips you should addSelect and leftJoin them inside your query as follows:

$results = $qb
    ->select('ps')
    ->addSelect('u')->leftJoin('ps.author','u')
    ->addSelect('v')->leftJoin('u.vip','v')
    ->add('where', $qb->expr()->in('ps.id', $ids))
    ->getQuery()->getResult();

UPDATE

Update after your comment:

I thought including vip in the result set would be the best way to get rid of the extra query

You cannot get rid of the extra query because you cannot lazy load the inverse side of a one-to-one relationship. Refer also to this post for more details:

This is expected behavior. Inverse sides of one-to-one associations can not be lazy, technically. There is no foreign key on the inverse side, hence it is impossible to decide whether to proxy it or not. We must query for the associated object or join it. Note that this only affects inverse sides of single-valued associations, that is, really only the inverse side of bidirectional one-to-one associations.

  • A solution could be to inverse the relationship so user becomes the owning side of the relationship. I that case you can at least lazy-load Vip inside your User entity. The lazy load problem would move to the Vip side, meaning you could not lazy-load your User in Vip any longer.

  • Otherwise you could make your query return a Partial object to prevent loading of Vip, but in general you should be very careful with this approach.

Saturday, May 29, 2021
 
jab
answered 5 Months ago
jab
55

I've discovered the source of the problem somewhere else, and solving it has made unnecessary to do what asked in the question; as described in other answers, it should be unnecessary to perform the distinct here.

The duplicate rows were originated by erroneous left joins that were performed on collections (attributes of the root object) even if the predicates were not been used:

Join<Lawsuit, Witness> witnesses = root.join("witnesses", JoinType.LEFT);
if (witnessToFilterWith!=null) {
    predicateList.add(cb.equal(witnesses.<Long>get("id"),witnessToFilterWith.getId()));
}

The join should obviously be performed as inner and only if needed:

if (witnessToFilterWith!=null) {
    Join<Lawsuit, Witness> witnesses = root.join("witnesses", JoinType.INNER);
    predicateList.add(cb.equal(witnesses.<Long>get("id"),witnessToFilterWith.getId()));
}

So, if you're here because you're getting the same problem, search the problem in the joins.

Thursday, August 12, 2021
 
Gersom
answered 3 Months ago
82

Unfortunately, This is not possible. Per here:

https://groups.google.com/forum/#!topic/doctrine-user/0rNbXlD0E_8

You can do it using IN here:

Doing a WHERE .. IN subquery in Doctrine 2

Saturday, August 14, 2021
 
Byron Whitlock
answered 3 Months ago
44

The query builder is exactly there for building conditional queries. You could do:

$qb = $this->_em->createQueryBuilder();

$query = $qb->select('u')
            ->from('Users', 'u')
            ->where('u.id = ?1')                   
            ->setParameter(1, $userid);

if ($status) {
    $qb->andWhere('u.status = ?2')
       ->setParameter(2, $status);
}

return $qb->getQuery()->getResult();

On a side note, it is best practice to use named placeholders e. g. like this:

    $qb->andWhere('u.status = :status')
       ->setParameter('status', $status);
Monday, October 25, 2021
 
Geetha
answered 1 Day ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share