Facing Issue With Table Relations

Hi,

[color="#008000"]Help me to write correct Model code and relations.[/color]

See here: tables and relation mapping by model class.

See this image to understand relations: http://ubuntuone.com…nuQTmhbCIryNtLJ

Download file for model class example to show relations: 3795

ask.php

Querying:




Post::model()

->with(array(

	'termRelations' => array(

    	'with' => array(

        	'termTaxonomy' => array(

            	'condition' => "\"termTaxonomy\".type='ARTICLE'",

            	'with' => 'term',

        	),

    	),

	),

))->limit(5)->findAll("post_status='PUBLISHED'");



The Above Script is generating two different SQL below:




SELECT

	"t"."id" AS "t0_c0",

	"t"."post_type" AS "t0_c1",

	"t"."post_status" AS "t0_c2",

	"t"."post_format" AS "t0_c3",

	"t"."content" AS "t0_c4"

FROM

	"table_post" "t"

WHERE (post_status='PUBLISHED') LIMIT 5



AND




SELECT

	"t"."id" AS "t0_c0",

	"t"."content" AS "t0_c1",

	"termRelations"."id_ref" AS "t1_c0",

	"termRelations"."id_term_taxonomy" AS "t1_c1",

	"termTaxonomy"."id" AS "t2_c0",

	"termTaxonomy"."id_term" AS "t2_c1",

	"termTaxonomy"."type" AS "t2_c2",

	"term"."id" AS "t3_c0",

	"term"."name" AS "t3_c1",

	"term"."slug" AS "t3_c2"

FROM

	"table_post" "t"

LEFT OUTER JOIN "table_term_relation" "termRelations" ON ("termRelations"."id_ref"="t"."id")

LEFT OUTER JOIN "table_term_taxonomy" "termTaxonomy" ON ("termRelations"."id_term_taxonomy"="termTaxonomy"."id")

LEFT OUTER JOIN "table_term" "term" ON ("termTaxonomy"."id_term"="term"."id")

WHERE

	("t"."id" IN ('37', '38', '39', '40', '41')) AND ("termTaxonomy".type='ARTICLE')



[color="#FF0000"]Generated SQL Statement doesn’t having JOIN on table TermRelations[/color]

[color="#00BFFF"]But this should be generate only one script like I’ve manually written:[/color]




SELECT

	"t"."id" AS "t0_c0",

	"t"."content_markdown" AS "t0_c1",

	"termRelations"."id_ref" AS "t1_c0",

	"termRelations"."id_term_taxonomy" AS "t1_c1",

	"termTaxonomy"."id" AS "t2_c0",

	"termTaxonomy"."id_term" AS "t2_c1",

	"termTaxonomy"."type" AS "t2_c2",

	"term"."id" AS "t3_c0",

	"term"."name" AS "t3_c1",

	"term"."slug" AS "t3_c2"

FROM

	"table_post" "t"

LEFT OUTER JOIN "table_term_relation" "termRelations" ON ("termRelations"."id_ref"="t"."id")

LEFT OUTER JOIN "table_term_taxonomy" "termTaxonomy" ON ("termRelations"."id_term_taxonomy"="termTaxonomy"."id")

LEFT OUTER JOIN "table_term" "term" ON ("termTaxonomy"."id_term"="term"."id")

WHERE

	("t"."post_status"='PUBLISHED') AND ("t"."id" = "termRelations"."id_ref") AND ("termTaxonomy".type='ARTICLE')



[color="#F5DEB3"]NOTE: Database I’m using PostgreSQL 9.1 [/color]

But where do you put “LIMIT 5” clause? :)

If you have set "limit" to the primary model, then you will not be able to load HAS_MANY related model in eager loading manner. It means that the table will not be joined. Just try findAll() without limit(), then you will get what you are expecting.

You can set “together” to true to force the eager loading (joining tables), but I’m sure you will not be satisfied with the fetched result.

http://www.yiiframework.com/doc/guide/1.1/en/database.arr#relational-query-performance

http://www.yiiframework.com/wiki/428/drills-search-by-a-has_many-relation

[P.S.]

I would like you to recommend to show your code in the post using "code" tag. Generally not many people are willing to download an external file. You are missing a lot of possible readers.

And also the image file should be attached to the post.

Thanks @softark,

I tried as you suggested and it is now working…

I found another solution:




TermRelation::model()

->with(array(

	'post' => array(

    	'condition' => "\"post\".\"post_status\"='PUBLISHED'",

	),

	'termTaxonomy' => array(

    	'condition' => "\"termTaxonomy\".\"type\"='ARTICLE'",

    	'with' => 'term',

	),

))

->limit(5)

->findAll();



And as you suggested the code is:




Post::model()

->with(array(

	'termRelations' => array(

    	'with' => array(

        	'termTaxonomy' => array(

            	'condition' => "\"termTaxonomy\".type='ARTICLE'",

            	'with' => 'term',

        	),

    	),

    	'together' => TRUE,

	),

))

->limit(5)

->findAll("post_status='PUBLISHED'");



Thanks again

:)

I see. Using TermRelation instead of Post should be a good idea.

Well, is TermRelation actually a HAS_ONE relation when filtered by “type”? Then I think it’s OK.

Now I want to add cache dependency for above script.

Suggest me to write perfect cache dependency for faster performance.

Well, it’s totally another story.

In general, you have to list all the conditions that should discard the cache entry, in the first place.

You will want to discard the cache when the post table has been modified. And you will also want to discard it when the related tables have been modified.

You have to use CChainedCacheDependency when you have to deal with multiple conditions.

http://www.yiiframework.com/doc/api/1.1/CChainedCacheDependency

Each dependency that belongs to the CChainedCacheDependency can be defined as a CDbCacheDependency. But you should be careful to use a light-weight SQL for it, otherwise the overhead of the dependency checking will cancel all the benefits of the caching.

This thread might be worth reading.

http://www.yiiframework.com/forum/index.php/topic/37500-cdbcachedependency-effective-sql-that-handles-modifications-and-deletions