Select entries between dates in doctrine 2

Question 1

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

Question 2

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'));

Question 3

Firstly, don't worry too much about the database design. You should design your entities and use the SchemaTool.

Now, the question is, is there a simple way of accessing all the orders from the user model?

Do you mean access all of the orders from the user model, or access all the orders associated to a user?

If you meant the former, well you are doing things wrong (see below). If you meant the later, you should setup a bi-directional relationship between orders and users. (BTW, it would be OneToMany not OneToOne as one user would likely have many orders).

I'm really confused in this very basic model...

I think what you having trouble with - along with many PHP programmers - is the fundamental understandings of the DataMapper pattern and ultimately Domain Driven Design as well. Remember, you are dealing with persistable objects, not database tables.

I cannot provide detailed information here because I'd be writing a book, hence this I would recommend you get a book on Domain Driven Design to help kick start with the principles. There are a few good online resources available, like a series of blog posts by Federico Cargnelutti, however they aren't specific to Doctrine 2.

Question 4

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.

Question 5

you could write

select DISTINCT f from t;

as

select f from t group by f;

thing is, I am just currently myself getting into Doctrine, so I cannot give you a real answer. but you could as shown above, simulate a distinct with group by and transform that into Doctrine. if you want add further filtering then use HAVING after group by.

Question 6

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

Select entries between dates in doctrine 2

Edit 1

Answers

UPDATE

php,date,doctrine-orm,between,query-builder