SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict hiddenpearls's Avatar
    Join Date
    Dec 2007
    Location
    Pakistan
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Left joins halting mysql server, Wordpress Custom Query

    hi guys, I'm facing a strange problem with SQL in WP custom query. when I add the Left Joins specially these

    Code SQL:
     LEFT JOIN wp_postmeta wpostmeta2 ON wposts.ID = wpostmeta2.post_id
     LEFT JOIN wp_postmeta wpostmeta3 ON wposts.ID = wpostmeta3.post_id
     LEFT JOIN wp_postmeta wpostmeta4 ON wposts.ID = wpostmeta4.post_id

    in the following query

    Code SQL:
    SELECT DISTINCT wposts.* FROM wp_posts wposts 
    LEFT JOIN wp_postmeta wpostmeta ON wposts.ID = wpostmeta.post_id
     LEFT JOIN wp_postmeta wpostmeta2 ON wposts.ID = wpostmeta2.post_id
     LEFT JOIN wp_postmeta wpostmeta3 ON wposts.ID = wpostmeta3.post_id
     LEFT JOIN wp_postmeta wpostmeta4 ON wposts.ID = wpostmeta4.post_id 
    LEFT JOIN wp_term_relationships ON (wposts.ID = wp_term_relationships.object_id) 
    LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) 
    WHERE (wposts.post_type = 'post' OR wposts.post_type = 'page' OR wposts.post_type = 'storhushall') 
    AND wposts.post_status = 'publish' 
    AND (wp_term_taxonomy.taxonomy = 'wb_business_recept_category' OR wp_term_taxonomy.taxonomy = 'wb_business_product_category' 
    OR post_parent=12
    OR (wpostmeta2.meta_key = 'art_nr' AND wpostmeta2.meta_value LIKE '%7023%')
     OR (wpostmeta3.meta_key = 'andra_versioner' AND wpostmeta3.meta_value LIKE '%7023%') OR (wpostmeta4.meta_key = 'viktpaket' AND wpostmeta4.meta_value LIKE '%7023%') OR (wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN (11,13))) GROUP BY wposts.ID

    it halts and takes forever to load in MYSQL. and when I run it at front end. it gives Internal server error.

    what I'm doing wrong ?

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Putting conditions on the left joined tables in the WHERE clause makes those joins behave like INNER JOINs.

    And did you try running that query in phpMyAdmin?

  3. #3
    SitePoint Addict hiddenpearls's Avatar
    Join Date
    Dec 2007
    Location
    Pakistan
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Putting conditions on the left joined tables in the WHERE clause makes those joins behave like INNER JOINs.

    And did you try running that query in phpMyAdmin?
    I don't have PMA installed at my server. It is VisualMin which allows to run Execute SQL on server. So, Yes I tried and it goes forever to load.
    I'm using joins in where clause, because I wanna search from custom fields too. Thanks

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Ok, maybe since you're using OR's it might work. But I'm wondering why you have to join the same table four times? Wouldn't once be enough? :
    Code:
    SELECT DISTINCT wposts.* 
    FROM wp_posts AS wposts 
    LEFT JOIN wp_postmeta AS wpostmeta 
    ON wposts.ID = wpostmeta.post_id
    LEFT JOIN wp_term_relationships 
    ON wposts.ID = wp_term_relationships.object_id
    LEFT JOIN wp_term_taxonomy 
    ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
    WHERE wposts.post_type IN ('post', 'page', 'storhushall') 
    AND wposts.post_status = 'publish' 
    AND (   wp_term_taxonomy.taxonomy = 'wb_business_recept_category' 
         OR wp_term_taxonomy.taxonomy = 'wb_business_product_category' 
         OR post_parent=12
         OR (wpostmeta.meta_key = 'art_nr' AND wpostmeta.meta_value LIKE '%7023%')
         OR (wpostmeta.meta_key = 'andra_versioner' AND wpostmeta.meta_value LIKE '%7023%') 
         OR (wpostmeta.meta_key = 'viktpaket' AND wpostmeta.meta_value LIKE '%7023%') 
         OR (wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN (11,13))
       ) 
    GROUP BY wposts.ID

  5. #5
    SitePoint Addict hiddenpearls's Avatar
    Join Date
    Dec 2007
    Location
    Pakistan
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Ok, maybe since you're using OR's it might work. But I'm wondering why you have to join the same table four times? Wouldn't once be enough? :
    Code:
    SELECT DISTINCT wposts.* 
    FROM wp_posts AS wposts 
    LEFT JOIN wp_postmeta AS wpostmeta 
    ON wposts.ID = wpostmeta.post_id
    LEFT JOIN wp_term_relationships 
    ON wposts.ID = wp_term_relationships.object_id
    LEFT JOIN wp_term_taxonomy 
    ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
    WHERE wposts.post_type IN ('post', 'page', 'storhushall') 
    AND wposts.post_status = 'publish' 
    AND (   wp_term_taxonomy.taxonomy = 'wb_business_recept_category' 
         OR wp_term_taxonomy.taxonomy = 'wb_business_product_category' 
         OR post_parent=12
         OR (wpostmeta.meta_key = 'art_nr' AND wpostmeta.meta_value LIKE '%7023%')
         OR (wpostmeta.meta_key = 'andra_versioner' AND wpostmeta.meta_value LIKE '%7023%') 
         OR (wpostmeta.meta_key = 'viktpaket' AND wpostmeta.meta_value LIKE '%7023%') 
         OR (wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN (11,13))
       ) 
    GROUP BY wposts.ID
    it worked like a charm. I guess problem was useless joins. Thanks


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •