sqlobject-discuss

[SQLObject] left join with subquery and WHERE

[Imre H. <ble...@gm...>]

Hi!

I wrote a small program to test this problem.
I have 3 tables:

CREATE TABLE pricegroups (id integer primary key);
CREATE TABLE products (id integer primary key, name text);
CREATE TABLE products_pricegroups (id integer primary key, product_id
integer, pricegroup_id integer, percent integer);

I would like to perform the following query:

select * from pricegroups 
left join (select * from products_pricegroups
where products_pricegroups.product_id=1) as a 
on (pricegroups.id=a.pricegroup_id)

How can I accomplish it in mysql?
This is where i am now:

#!/usr/bin/env python

import os
import sqlobject

class Product(sqlobject.SQLObject):

	class sqlmeta:
		table = 'products'

	name = sqlobject.StringCol()

class PriceGroup(sqlobject.SQLObject):

	class sqlmeta:
		table = 'pricegroups'

	percents = sqlobject.MultipleJoin('ProductPriceGroup', joinColumn =
'pricegroup_id')
	
class ProductPriceGroup(sqlobject.SQLObject):

	class sqlmeta:
		table = 'products_pricegroups'

	productId = sqlobject.ForeignKey('Product', dbName = 'product_id')
	pricegroupId = sqlobject.ForeignKey('PriceGroup', dbName =
'pricegroup_id')
	percent = sqlobject.IntCol()

if __name__ == '__main__':

	builder = sqlobject.sqlite.builder()
	connection = builder('join.db')
	connection.debug = 1
	sqlobject.sqlhub.processConnection = connection

	pgl = list(PriceGroup.select(
		join = sqlobject.sqlbuilder.LEFTJOINOn(
			PriceGroup, ProductPriceGroup,
			PriceGroup.q.id == ProductPriceGroup.q.pricegroupId,
			),
		))
	
	for pg in pgl:
		print pg

The next step is to apply the WHERE clause, but I have no idea how to do
it...

Thanks for any help:
Imre Horvath

Re: [SQLObject] left join with subquery and WHERE

[Oleg B. <ph...@ph...>]

On Fri, May 14, 2010 at 03:37:46PM +0200, Imre Horvath wrote:
> select * from pricegroups 
> left join (select * from products_pricegroups
> where products_pricegroups.product_id=1) as a 
> on (pricegroups.id=a.pricegroup_id)

   Currently it's impossible to do with SQLObject as it assumes both sides
of a JOIN are table names, not a subselect. That could be fixed.

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] left join with subquery and WHERE

[Oleg B. <ph...@ph...>]

On Fri, May 14, 2010 at 06:22:05PM +0400, Oleg Broytman wrote:
> On Fri, May 14, 2010 at 03:37:46PM +0200, Imre Horvath wrote:
> > select * from pricegroups 
> > left join (select * from products_pricegroups
> > where products_pricegroups.product_id=1) as a 
> > on (pricegroups.id=a.pricegroup_id)

   Does the query work at all in MySQL?

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] left join with subquery and WHERE

[Imre H. <ble...@gm...>]

On Fri, 2010-05-14 at 18:24 +0400, Oleg Broytman wrote:
> On Fri, May 14, 2010 at 06:22:05PM +0400, Oleg Broytman wrote:
> > On Fri, May 14, 2010 at 03:37:46PM +0200, Imre Horvath wrote:
> > > select * from pricegroups 
> > > left join (select * from products_pricegroups
> > > where products_pricegroups.product_id=1) as a 
> > > on (pricegroups.id=a.pricegroup_id)
> 
>    Does the query work at all in MySQL?
> 
> Oleg.

It's in sqlite, but it works.
It's also works in postgres.
The goal is to select all pricegroups for a product, even if it's not
set for the given product.
this is my test db:

sqlite> .schema
CREATE TABLE pricegroups (id integer primary key);
CREATE TABLE products (id integer primary key, name text);
CREATE TABLE products_pricegroups (id integer primary key, product_id
integer, pricegroup_id integer, percent integer);

sqlite> select * from products;
1|a
2|b
3|v

sqlite> select * from pricegroups;
1
2
3
4
5
6
7
8

sqlite> select * from products_pricegroups;
1|1|1|10
2|1|2|20
3|2|4|20

sqlite> select * from pricegroups 
   ...> left join (select * from products_pricegroups
   ...> where products_pricegroups.product_id=1) as a 
   ...> on (pricegroups.id=a.pricegroup_id);
1|1|1|1|10
2|2|1|2|20
3||||
4||||
5||||
6||||
7||||
8||||

sqlite> select * from pricegroups 
   ...> left join (select * from products_pricegroups
   ...> where products_pricegroups.product_id=2) as a 
   ...> on (pricegroups.id=a.pricegroup_id);
1||||
2||||
3||||
4|3|2|4|20
5||||
6||||
7||||
8||||


I hope i was clear

Regards:
Imre Horvath

Re: [SQLObject] left join with subquery and WHERE

[Oleg B. <ph...@ph...>]

On Fri, May 14, 2010 at 04:43:46PM +0200, Imre Horvath wrote:
> sqlite> select * from pricegroups 
>    ...> left join (select * from products_pricegroups
>    ...> where products_pricegroups.product_id=1) as a 
>    ...> on (pricegroups.id=a.pricegroup_id);

   I don't understan what is aliased as 'a'. The subquery? I tried to take
it into parentheses:

select * from pricegroups 
   left join ((select * from products_pricegroups
   where products_pricegroups.product_id=1) as a)
   on (pricegroups.id=a.pricegroup_id);

bug got an error:

   SQL error near line 1: no such column: a.pricegroup_id.

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] left join with subquery and WHERE

[Imre H. <ble...@gm...>]

On Fri, 2010-05-14 at 19:21 +0400, Oleg Broytman wrote:
> On Fri, May 14, 2010 at 04:43:46PM +0200, Imre Horvath wrote:
> > sqlite> select * from pricegroups 
> >    ...> left join (select * from products_pricegroups
> >    ...> where products_pricegroups.product_id=1) as a 
> >    ...> on (pricegroups.id=a.pricegroup_id);
> 
>    I don't understan what is aliased as 'a'. The subquery? I tried to take
> it into parentheses:
> 
> select * from pricegroups 
>    left join ((select * from products_pricegroups
>    where products_pricegroups.product_id=1) as a)
>    on (pricegroups.id=a.pricegroup_id);
> 
> bug got an error:
> 
>    SQL error near line 1: no such column: a.pricegroup_id.
> 
> Oleg.

Yes, the subquery has to be aliased.

I found a less elegant but working solution:
Select all pricegroups, then select the groups_pricegroups of a product,
and combine the 2 results manually...

Imre