join subquery

[Sergiy Zuban]

Hi Laurent,

I need to make the following query:

select f.id, f.name, z.bar_count

from Foo f

  join (

       select foo_id, count(*) as bar_count

       from Bar group by foo_id

   ) as z

on z.foo_id = f.id

where z.bar_count > 0

I know about -result_as => 'subquery', but as far as I see it can be used in -where only.

It would be nice to extend "join" method somehow to allow calls like:

 $schema->join( 'Foo|f', '<=>', $subquery )->select( -where_on => { Foo => { id => { -ident => 'foo_id' } } } )

Since custom subquery used there is no easy way to define association. Let's why join operator/type and condition become required.

What do you think about this idea? I feel that i'm doing something wrong and there is easier way.

PS.

I can get the same result with the query below, but it requires grouping by all  non-

aggregatable columns, but it works couple times slower in my case.

select f.id, f.name, count(*)

from Foo f

  join Bar b on b.foo_id = f.id

group by f.id,f.name

having count(*) > 0

--
Sergiy Zuban

[Dami Laurent (PJ)]

Hi Sergiy,

 

I had never seen a join between a table and an anonymous select, I didn’t even know this was possible.

Unfortunately, DBIx ::DataModel cannot support this, because there is no way to construct a class for blessing the resulting rows. So I’m afraid you have to stick with your second solution. Another possibility would be to use a virtual column and/or a custom function like « count_Bar_occurrences(f.id) » ; I’ve done such things in Oracle but this is not standard SQL so you have to check if such a thing  is possible in your DBMS.

 

Cheers, Laurent Dami

 

 

 

 

 

 

De : dbix-da...@googlegroups.com [mailto:dbix-da...@googlegroups.com] De la part de Sergiy Zuban
Envoyé : mercredi 21 mai 2014 23:40
À : dbix-da...@googlegroups.com
Objet : [dbix-datamodel] join subquery

--

---
You received this message because you are subscribed to the Google Groups "dbix-datamodel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dbix-datamode...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Sergiy Zuban]

Thanks for quick reply. I suspected it's not possible.

--
Sergiy Zuban

[Ross Attrill]

What if you made the inner select into a view and then treated that as an entity with DBIDM?

--

[Sergiy Zuban]

I was thinking about that as well. View can be declared with plain SQL, but I want to generate inner select via DBIDM as well.

--
Sergiy Zuban