[freeside-devel] mysql and complex queries

[ivan]

On Tue, Nov 20, 2001 at 12:29:50PM -0600, Dave Burgess wrote:
> OK.
> 
> I've been through the BNF for the SELECT statement in MySQL, and Version
> 3 doesn't like this particular syntax at all.

Do you mean to say it works in version 4?

> - The parentheses might be a problem.  I wasn't able to use them for
> grouping.

Hmm, I thought I only used them where required by the JOIN syntax or for
sub-queries.  Is MySQL missing support for subqueries?

> - Even without those, cascading OUTER JOINS need to have a specific
> syntax:
> 
>   SELECT * FROM table LEFT OUTER JOIN table_reference USING (field_list)
> LEFT OUTER JOIN table_reference ON conditional ;
> 
> USING and ON clauses can be interchanged.

This looks to be the same as the PostgreSQL syntax for joins:
http://www.postgresql.org/users-lounge/docs/7.1/postgres/sql-select.html

> So, the query below is OK.
> 
> SELECT * FROM cust_main left outer join cust_pkg using (custnum) left
> outer join part_pkg using (pkgpart);
> 
> This one fails:
> 
> SELECT * FROM cust_main left outer join cust_pkg using  (custnum)
>     left outer join part_pkg using (pkgpart)
>       left outer join cust_svc
> 
> It fails on the final "LEFT OUTER JOIN", apparently because there's no
> obvious way to connect these tables....

Hmm, I'm pretty sure I used an "ON" or "USING" qualifier on all the joins
in the query below.

> If you don't mind me asking,

I certainly do not mind.

> is this just a thought exercise, or are we
> actually trying to accomplish something in particular.

This is not a thought exercise.  This is the actual SQL used by the
customer search page in CVS (httempalte/search/cust_main.cgi).  It's
significantly faster to use one large query rather than thousands of small
queries, and sped up the customer search for large data sets
significantly. 

>  For example, I
> don't see how we can connect part_pkg and cust_svc like this last join
> is trying.

Hmm, I'm not joining part_pkg and cust_svc, I'm joining cust_pkg to
part_pkg, and also cust_pkg to cust_svc.

> If I modify the select so that it tries to connect the cust_svc.pkgnum
> key with the one in the cust_pkg (like this):
> 
> SELECT * FROM cust_main left outer join cust_pkg using  (custnum)
>     left outer join part_pkg using (pkgpart)
>       left outer join cust_svc using (pkgnum)
> 
> It fails again (ERROR 1054: Unknown column 'part_pkg.pkgnum' in 'on
> clause')
> 
> If I replace the using(pkgnum) with "on cust_svc.pkgnum =
> cust_pkg.pkgnum" the SELECT works again.
> 
> SELECT * FROM cust_main left outer join cust_pkg using  (custnum)
>     left outer join part_pkg using (pkgpart)
>       left outer join cust_svc on cust_svc.pkgnum = cust_pkg.pkgnum
> 
> So, complex LEFT JOINS are possible, it might just take a little more
> study to get the query to work in MySQL.

I pretty much only develop with PostgreSQL now, so if MySQL support is
important to the Freeside-using community, the community needs to help
keep the MySQL support current.  Let me know, guys.  As it looks now,
1.4.0pre6 will not include MySQL support unless someone makes it happen. 

> BTW:  MySQL Version 4 supports UNION now, so we might be able to use
> that to accomplish this.

If it's easy to support MySQL v4 but difficult to support MySQL v3, I
don't mind dropping support for MySQL 3.

> 
> 
> ivan wrote:
> 
> > can mysql handle this query?  if not, what would need to be changed?
> >
> > SELECT * FROM cust_main left outer join (
> >   ( cust_pkg left outer join part_pkg using(pkgpart)
> >   ) left outer join (
> >     (
> >       (
> >         ( cust_svc left outer join part_svc using (svcpart)
> >         ) left outer join (
> >           svc_acct left outer join (
> >             select svcnum, domain, catchall from svc_domain
> >             ) as svc_acct_domsvc (
> >               svc_acct_svcnum, svc_acct_domain, svc_acct_catchall
> >           ) on svc_acct.domsvc = svc_acct_domsvc.svc_acct_svcnum
> >         ) using (svcnum)
> >       ) left outer join svc_domain using(svcnum)
> >     ) left outer join svc_forward using(svcnum)
> >   ) using (pkgnum)
> > ) using (custnum)
> >
> >
> > (apologies if the SQL indentation is weird)
> >
> > --
> > _ivan
> 

-- 
_ivan