Re: query

We have complicated queries that span across 8 tables. We decided to use pure SQL code. We defined our data source and run SQL through it defining our custom methods in the model that execute the query. If the query is just on one table we use the ORM but pure SQL pretty much in the other cases. The purpouse of the ORM layer is to make things easier. If you make it  more complicated, why are you doing it?

On Oct 31, 2011 1:55 AM, "WebbedIT" <paul@webbedit.co.uk> wrote:

Without seeing any of your code (model associations would have been
nice) I logically assume you are using Article HABTM Tag.

If so this does not create a model for the join table, but instead
puts in place some automagic to allow you to link many-to-many records
between the two models.  As such you can't run a find on the join
model, which is what you would have to do to be able to have
conditions spanning the three tables.

Your options are to use unbind and bind to force joins that you can
use:
http://nuts-and-bolts-of-cakephp.com/2008/08/06/habtm-and-join-trickery-with-cakephp/

Or, my preferred option is to create a model for join tables

Article hasMany ArticleTag
Tag hasMany ArticleTag
ArticleTag belongsTo Article, Tag

This allows you to easily add extra fields into your join table and to
run $this->Article->ArticleTag->find('all', array('conditions'=>array(
 'Article.is_read'=>0,
 'Article.status'=>1,
 'Tag.name'=>'cakephp'
)));

HTH, Paul.

P.S. Notice how I have changed some of your field names and values.
Your isRead field should be lowercased and underscored and if boolen
use tinyint(1) with 0 and 1.  When searching for articles by tag you
would normally recieve the tagname in the request (much better for
seo) so to query by Tag.id would require an extra find that I would
say is unneccessary when you can filter by Tag.name (especially if you
have the name field unique indexed).  I would also switch
Article.status for numerical values, ideally with a lookup table so
you can easily add/edit status text across all articles.



On Oct 30, 8:50 am, SERKAN TURAN <serkantu...@gmail.com> wrote:
> Hi,
> I have an query and I could not figure it out what is the cakephp form of
> it.
>
> query:
> -------------------------------------------
> SELECT *
> FROM
>   tags`
>   INNER JOIN `tags_articles` ON (`tags`.`id` = `tags_articles`.`tag_id`)
>   INNER JOIN `articles` ON (`tags_articles`.`article_id` = `articles`.`id`)
> WHERE
>   `articles`.`isRead` = 'Not' AND
>   `tags`.`id` = 3 AND
>   `articles`.`status` = 'New'
> ORDER BY
>   `articles`.`name` DESC
> --------------------------------------------------------------------------- -------------------
>
> Thnks..
> ST

--
Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions.


To unsubscribe from this group, send email to
cake-php+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php

--
Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions.
 
 
To unsubscribe from this group, send email to
cake-php+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php