ansaurus

Question

Answer 1

+1 A:

Have you tried:

'SELECT p FROM Product p WHERE SIZE(p.reservations) < p.nr_of_bookings'

EDIT: The above is for Doctrine 2. For Doctrine 1.2, looking at your code, I'm guessing your HAVING clause is referencing something that's not in a group by or the result of an aggregate function. Try something like this:

$q = PropertyTable::getInstance()
    ->createQuery('p') 
    ->select('p.*, COUNT(r.id) as num_of_reservations, SUM(p.nr_of_bookings) as num_bookings') 
    ->leftJoin('p.Reservation r') 
    ->groupBy('p.id') 
    ->having('num_of_reservations < num_bookings');

You're grouping by p.id so SUM(p.nr_of_bookings) will be equal to p.nr_bookings.

From the MySQL documentation:

The SQL standard does not permit the HAVING clause to name any column not found in the GROUP BY clause if it is not enclosed in an aggregate function.

rojoca 2010-10-26 16:53:59

Basic DQL is like this:

Zeljko 2010-10-26 23:18:48

$q = PropertyTable::getInstance()->createQuery('p') ->select('p.*, COUNT(r.id) as num_of_reservations') ->groupBy('p.id') ->leftJoin('p.Reservation r') ->having('num_of_reservations < p.nr_of_bookings') ; I would later add some extra conditions like: ->leftJoin('p.Reservation r WITH r.start_date>', date('Y-m-d')) But once this basic one is written correctly. Thanks

Zeljko 2010-10-26 23:50:02

Oh are you using Doctrine 1.2?

rojoca 2010-10-27 00:58:56

I am using Doctrine 1.2 . I will try that SIZE(p.reservations) but I can't find any documentation for SIZE command.

Zeljko 2010-10-28 11:23:03

I tried SUM(p.nr_of_bookings) as num_bookings' solution, but it didn't give me correct results. When I print_r results, 'num_bookings' is much higher number that it should be. After that, I placed this: $q->groupBy('p.id,p.nr_of_bookings') ->addHaving('num_of_reservations<p.nr_of_bookings') ; For a moment, this looks like results are correct based on my Unit tests. Another question; it grouping by nr_of_bookings a good idea? Does this aggreagate function work on single property or will affect other properties somehow? Property and Reservations are one-2-many related. Thanks.

Zeljko 2010-10-29 13:54:50

Interesting thing: instead of SUM(p.nr_of_bookings), I placed this: ROUND(p.nr_of_bookings) as num_bookings This would fullfill Mysql demand for aggreate function and yet, not make any calculations. Also; groupBy('p.id,p.nr_of_bookings') didn't work after all. Can you tell me if something is wrong using that ROUND() aggregate function?

Zeljko 2010-10-29 15:25:46

@Zeljko - `ROUND()` isn't supported in DQL (according to the docs). I just realized that `SUM(p.nr_of_bookings) = nr_of_bookings * num_reservations` because you join on reservations and then group them by id. Try this: `SUM(p.nr_of_bookings) / COUNT(r.id) as num_bookings` and see if it gives you the correct results

rojoca 2010-10-29 15:45:54

YES, it was exactly what happened. About ROUND() ; interesting thing is this actually works VERY nice. I have written many unit tests and no fails so far. But, I trust your judgement and will also try that other suggestion later. Thanks again...

Zeljko 2010-10-29 18:28:25

@Zeljko - If `ROUND()` works - great! I just couldn't find it in the documentation which is why I made the other suggestion.

rojoca 2010-10-29 18:32:34

ansaurus

tags:

views:

answers:

I need help with complex join

related questions