Re: [fw-db] how to use zend db with joins

If I need to get the DB for a custom query or so, I usually do it like this (be sure to set it as default adapter in the config) :

$db = Zend_Db_Table::getDefaultAdapter();

That way you have an instantiated db object. If you haven't used it yet, the connection is not open yet. Once you have used it once, all DB queries will use the same connection.

Now, if you want to show your list on page 1 and then update on page 2 (so in different requests), your DB connection will be remade. It's standard behaviour unless you use persistent connections... but I'm not a fan of those.

Regarding best practices: as long as you put the queries in your data layer, it doesn't matter how you use it in my opinion. Just don't put plain SQL in your controller or domain models.

Wkr

Jeroen

On 23 Jun 2010, at 11:25, Timmo Henseler wrote:

Jeroen, thanks, yes this helps a lot. when i use raw queries for update can I then just assume that the database connection is still there (I read something about lazy connections) or do I have to approach the database with regular mysqli? Is it good practice to mix zend db code with raw php/mysqli for the same model/database?

Timmo

On 23 June 2010 11:12, Jeroen Keppens <jeroen.keppens@gmail.com> wrote:

I would say that Zend_Db_Select is the way to go to do your joins in selects:

Example from the docs:

1. // Build this query:
2. //   SELECT p."product_id", p."product_name"
3. //   FROM "products" AS p JOIN "line_items" AS l
4. //     ON p.product_id = l.product_id
5.  
6. $select = $db->select()
7.              ->from(array('p' => 'products'),
8.                     array('product_id', 'product_name'))
9.              ->join(array('l' => 'line_items'),
10.                     'p.product_id = l.product_id',
11.                     array() ); // empty list of columns

If you want fields from the second table add the column names in the example where it says "empty list of columns".

If you want to do updates on multiple tables using joins, you'll have to use raw queries since ZF db->update does not support that as far as I know.

Hope this helps!

Wkr

Jeroen

http://blog.amazium.com

On 23 Jun 2010, at 10:58, zensys wrote:

Just a general question on how to best utilise zend db. I have a few tables
which I want to display with join statements and update afterwards. Not a
very complicated schema but quite some fields which I want to select
individually. To my limited understanding of DB I have the following
options:

- instantiate an adapter and just go from there using db->query() etc.
- use db select
- instantiate a db table for each of my tables and use joins (or db select?)
from there.

From what I've read in the manual it seems like only use an adapter gives

the least overhead. Db select is only for complicated queries and anyway you
cannot update with joins. Instantiate db tables for each table also sounds
like some overhead and I do not see the advantages.

Anyone any thoughts on this?

And another thing: do I need to instantiate an adapter but have the
resource.db.params in my application.ini . In that case, is the adapter
already instantiated and if so how do I reference it. If not, using the
db_factory? But it asks for parameters.
--
View this message in context: http://zend-framework-community.634137.n4.nabble.com/how-to-use-zend-db-with-joins-tp2265294p2265294.html
Sent from the Zend DB mailing list archive at Nabble.com.