Yii Framework Forum: Facing Issue With Table Relations - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Facing Issue With Table Relations Rate Topic: -----

#1 User is offline   VINAY Kr. SHARMA 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 151
  • Joined: 13-September 11
  • Location:Gurgaon

Posted 28 January 2013 - 07:18 AM

Hi,

Help me to write correct Model code and relations.

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: Attached File  ask.php (1.32K)
Number of downloads: 2

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


Generated SQL Statement doesn't having JOIN on table TermRelations

But this should be generate only one script like I've manually written:
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')


NOTE: Database I'm using PostgreSQL 9.1
VINAY Kr. SHARMA
@vinaykrsharma
Facebook ID: vinaykrsharma
Blog

Co-operation comes from friendship, friendship comes from trust, and trust comes from kind-heartedness.
0

#2 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,085
  • Joined: 16-February 11
  • Location:Japan

Posted 28 January 2013 - 07:52 AM

View PostVINAY Kr. SHARMA, on 28 January 2013 - 07:18 AM, said:

But this should be generate only one script like I've manually written:
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')


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.yiiframew...ery-performance
http://www.yiiframew...s_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.
1

#3 User is offline   VINAY Kr. SHARMA 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 151
  • Joined: 13-September 11
  • Location:Gurgaon

Posted 28 January 2013 - 08:25 AM

View Postsoftark, on 28 January 2013 - 07:52 AM, said:

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.yiiframew...ery-performance
http://www.yiiframew...s_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
:)
VINAY Kr. SHARMA
@vinaykrsharma
Facebook ID: vinaykrsharma
Blog

Co-operation comes from friendship, friendship comes from trust, and trust comes from kind-heartedness.
0

#4 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,085
  • Joined: 16-February 11
  • Location:Japan

Posted 28 January 2013 - 08:32 AM

View PostVINAY Kr. SHARMA, on 28 January 2013 - 08:25 AM, said:

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


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

Quote

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'");


Well, is TermRelation actually a HAS_ONE relation when filtered by "type"? Then I think it's OK.
0

#5 User is offline   VINAY Kr. SHARMA 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 151
  • Joined: 13-September 11
  • Location:Gurgaon

Posted 28 January 2013 - 10:18 AM

Now I want to add cache dependency for above script.

Suggest me to write perfect cache dependency for faster performance.
VINAY Kr. SHARMA
@vinaykrsharma
Facebook ID: vinaykrsharma
Blog

Co-operation comes from friendship, friendship comes from trust, and trust comes from kind-heartedness.
0

#6 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,085
  • Joined: 16-February 11
  • Location:Japan

Posted 28 January 2013 - 11:32 AM

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.yiiframew...CacheDependency

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.yiiframew...s-and-deletions
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users