Re: [Phpgroupware-developers] 0.9.16 contacts communications problem

[Andreas Schiller]

Edgar,

> Just to be sure:
> /* $Id: class.sql_builder.inc.php,v 1.1.2.14 2003/10/21 01:08:15 alexbsa Exp $ 
> */

I am afraid, the query was out of version 1.1.2.13... but the resulting 
memory error was also with 1.1.2.14 (I'm not quite sure, whether I 
checked the query with 1.1.2.14... sorry)

> I have doubts about your query, this is because this kind of query
> shoot a bug on mysql (the version that come with woody), so I fixed
> sql_builder in order to put the more centric entity (taking by
> reference the distance 

no problem here.

> > I believe there is a serious (design-) problem while showing the
> > communications tab for a (any) contact.
>
> There is a misunderstand here, this is just for one, or a given number
> of contacts_id, the method of contacts that we are talking is:

I know.

> > I'm quite sure you can't handle this with a single non-nested sql
> > command, like you do.
> > right now. But I am quite sure you have to do 2 queries to get the
> > result you want.
>
>
> Could you say those queries and why I could not get it with a single
> one?
>
> After bugfix I have:
>
> SELECT
>         contact_comm.contact_id AS comm_contact_id,
>         contact_comm.comm_id AS key_comm_id,
>         contact_comm.preferred AS comm_preferred,
>         contact_comm.comm_data AS comm_data,
>         contact_comm.comm_descr_id AS comm_descr,
>         contact_comm_descr.descr AS comm_description
> FROM 
>      phpgw_contact_comm AS contact_comm
>
>      LEFT JOIN phpgw_contact_comm_descr AS contact_comm_descr 
>      ON contact_comm_descr.comm_descr_id = contact_comm.comm_descr_id 
>
> WHERE ((contact_comm.contact_id = 9))

This query will only result in those rows which contain comm_data for 
contact_id=9.

This is because all other rows have NULL or other contact_ids.

The correct query would be a nested one, something like

SELECT
        contact_comm.contact_id AS comm_contact_id,
        contact_comm.comm_id AS key_comm_id,
        contact_comm.preferred AS comm_preferred,
        contact_comm.comm_data AS comm_data,
        contact_comm.comm_descr_id AS comm_descr,
        contact_comm_descr.descr AS comm_description
FROM
        phpgw_contact_comm_descr AS contact_comm_descr

        RIGHT JOIN (

                SELECT
                        contact_comm.contact_id AS comm_contact_id,
                        contact_comm.comm_id AS key_comm_id,
                        contact_comm.preferred AS comm_preferred,
                                contact_comm.comm_data AS comm_data,
                        contact_comm.comm_descr_id AS comm_descr
                FROM
                        phpgw_contact_comm AS contact_comm
                WHERE ((contact_comm.contact_id = 9))
        ) AS contact_comm

     ON contact_comm_descr.comm_descr_id = contact_comm.comm_descr_id

(just from scratch without any testing)

This will first select those comm_data for the contact(s) in question 
and then do the join

> > I would love to lend you a helping hand (in fact there would be two
> > hands :-)). But I could not find documentation to the sql builder
>
> Reference is here:
>
> http://co.com.mx/~eald/phpdoc-sql/

Thx, I will have a look soon.

> And I not finished the explanation on http://docs.phpgroupware.org
> sorry.
>
>
> > class, and I found it's quite hard to understand it from the code...
>
> I could explain any method or section on request.

I will come back to you, but in fact I haven't understand the whole 
concept right know.

Andy