Querying multiple sql tables when using etemplate?

[Staffan_Emren]

Hello all,

I hope I’m posting this in the correct list, otherwise please correct me.

I’m using etemplate to write a db application for internal use at our company, more specifically keep track of peoples competences. Since we use egroupware as our groupware application, I thought it would be nice to incorporate this db into egw. However, since all users are already in the egw tables, I don’t want to duplicate things liken names in a table of my own, but instead use the data already present in egw_accounts and egw_addressbook, and connect them with my own data through the account_id.

So to get the names and account id’s, I want to use a query like the following:
SELECT egw_accounts.account_id,n_given,n_family FROM egw_addressbook,egw_accounts WHERE (egw_accounts.account_primary_group=-1) AND (egw_accounts.account_id=egw_addressbook.account_id) ORDER BY n_given;
However, I can’t understand how to do this with the functions available in so_sql, they all seem to operate only on the table associated with the application.
I would be most grateful if someone could show me what I should do to get this done.

Best regards

Staffan Emrén

[Ralf_Becker_6]

Use the $join parameter in search or read:

$join = ‘LEFT JOIN egw_addressbook USING(account_id)’;

If the join is the general behavior, I mostly reimplment the search and
read methods, using a default value of $join=true and doing something like:

if ($join === true) $join = self::DEFAULT_JOIN;

Ralf

Staffan Emrén schrieb:

Hello all,

I hope I’m posting this in the correct list, otherwise please correct me.

I’m using etemplate to write a db application for internal use at our
company, more specifically keep track of peoples competences. Since we use
egroupware as our groupware application, I thought it would be nice to
incorporate this db into egw. However, since all users are already in the
egw tables, I don’t want to duplicate things liken names in a table of my
own, but instead use the data already present in egw_accounts and
egw_addressbook, and connect them with my own data through the account_id.

So to get the names and account id’s, I want to use a query like the
following:
SELECT egw_accounts.account_id,n_given,n_family FROM
egw_addressbook,egw_accounts WHERE (egw_accounts.account_primary_group=-1)
AND (egw_accounts.account_id=egw_addressbook.account_id) ORDER BY n_given;
However, I can’t understand how to do this with the functions available in
so_sql, they all seem to operate only on the table associated with the
application.
I would be most grateful if someone could show me what I should do to get
this done.

Best regards

Staffan Emrén

–
Ralf Becker
eGroupWare Training & Support ==> http://www.egroupware-support.de
Outdoor Unlimited Training GmbH [www.outdoor-training.de]
Handelsregister HRB Kaiserslautern 3587
Geschäftsführer Birgit und Ralf Becker
Leibnizstr. 17, 67663 Kaiserslautern, Germany
Telefon +49 (0)631 31657-0

---

This SF.Net email is sponsored by the Moblin Your Move Developer’s challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/

---

eGroupWare-developers mailing list
eGroupWare-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/egroupware-developers

[Staffan_Emren]

Hi Mr Becker,

Thank you for your reply. After some testing, investigation and reading of source code, it seem’s I won’t be able to use the methods in so_sql for this query. The thing is, the applications table is not involved at all in this query, and I don’t want to do a sleect * either. I guess I have to write some code myself to do a direct sql query of the tables in question.

Best regards

Staffan Emrén

Use the $join parameter in search or read:

$join = ‘LEFT JOIN egw_addressbook USING(account_id)’;

If the join is the general behavior, I mostly reimplment the search and
read methods, using a default value of $join=true and doing something like:

if ($join === true) $join = self::DEFAULT_JOIN;

Ralf

Staffan Emrén schrieb:

Hello all,

I hope I’m posting this in the correct list, otherwise please correct me.

I’m using etemplate to write a db application for internal use at our
company, more specifically keep track of peoples competences. Since we use
egroupware as our groupware application, I thought it would be nice to
incorporate this db into egw. However, since all users are already in the
egw tables, I don’t want to duplicate things liken names in a table of my
own, but instead use the data already present in egw_accounts and
egw_addressbook, and connect them with my own data through the account_id.

So to get the names and account id’s, I want to use a query like the
following:
SELECT egw_accounts.account_id,n_given,n_family FROM
egw_addressbook,egw_accounts WHERE (egw_accounts.account_primary_group=-1)
AND (egw_accounts.account_id=egw_addressbook.account_id) ORDER BY n_given;
However, I can’t understand how to do this with the functions available in
so_sql, they all seem to operate only on the table associated with the
application.
I would be most grateful if someone could show me what I should do to get
this done.

Best regards

Staffan Emrén

–
Ralf Becker
eGroupWare Training & Support ==> http://www.egroupware-support.de
Outdoor Unlimited Training GmbH [www.outdoor-training.de]
Handelsregister HRB Kaiserslautern 3587
Geschäftsführer Birgit und Ralf Becker
Leibnizstr. 17, 67663 Kaiserslautern, Germany
Telefon +49 (0)631 31657-0

---

This SF.Net email is sponsored by the Moblin Your Move Developer’s challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/

---

eGroupWare-developers mailing list
eGroupWare-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/egroupware-developers