Re: [Phpgroupware-developers] 0.9.16 contacts communications problem

[Edgar Luna]

Andreas Schiller <address@hidden> writes:

> Hi there,
Hi.

> Right now I am trying the 0.9.16 branch, updated from CVS last night.
Just to be sure:
/* $Id: class.sql_builder.inc.php,v 1.1.2.14 2003/10/21 01:08:15 alexbsa Exp $ 
*/

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 

[see class.contacts_sql.inc.php: var $map, the second element of the
array by field the form is:

'field' => array('entity', distance),]


> 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:

function get_comm_contact_data($contact_id, $fields_to_search='')
In the inline doc, and the one that could be generated with phpdoc
says that this is the proposed functionality for this method.


> I'm quite sure you can't handle this with a single non-nested sql
> command, like you do.
Indeed, maybe the query is the one that is generating the
misunderstand.

the query MUST be something like:

SELECT comm_data, comm_descr, somes_id_that_could_be_used
FROM
        phpgw_contact_comm AS contact_comm

        LEFT  JOIN
        phpgw_contact_comm_descr AS contact_comm_descr        
        ON contact_comm.comm_descr_id = contact_comm_descr.comm_descr_id
 
        LEFT  JOIN
        phpgw_contact_comm_type AS contact_comm_type
        ON contact_comm_descr.comm_type_id = contact_comm_type.comm_type_id

WHERE
        contact_comm.contact_id = 239

Now I'm asking to Jonathan (jarg) if he really needs to join with
comm_type and comm_descr, he says he use comm_descr, so that join
stay, the other one don't make sense for the propouse of the method.
[snniped]

> I believe what you were trying to get is something like all comm_descr
> with filled in comm_data for contact_id.
yeap

> Without last join with contact_central and without the restriction to
> contact_id 239 you would get a full list of contacts with every
> comm_descr and filled comm_data where exists.
Yes, that is actually a bug that I introduced and did not notice, I
need to thanks you a lot for this.

> But restricting to one contact_id gives just those records where
> comm_data exists.

> I must commit I did not quite understand what you were trying to get
>> From the last join - and neither I understood the sql_builder class
This is a bug, the way I tried to do this is:
I create a `path' for all entities required via request,
builder_criteria or criteria methods. Plus the one that are needed to
join one table with another

For example if you want first_name and city, I need to join:

Person with contact with address.
You only asked for a field on person and another on address but I need
the add the contact table to the from part so could join that tables.

But sometimes, I did not notice that I'm finished with the entities
and I want to reach to the center table (the one that have the
distance = 0
defined with something like this:

var $map = array('contact_id' => array('phpgwapi.contact_central', '0'),

then it try to join with this. But this is a bug, I have solved it,
but need test that nothing broken before commit, wanna try?

About the sql_bulder class seems that is really complex, I must refine
the code for avoid this, but only things like this mail are what I
need to do that. This is the second thing that I thank you.

> 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))

> 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/

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.

> Greetz from Germany,
> Andy Schiller
Thanks a lot.


-- 
Edgar Antonio Luna Díaz - http://www.sogrp.com
Fingerprint: C008 5EAC 5272 AC8C 7589  4821 8B34 6166 8733 8310