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
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]