WordPress.org

Ready to get started?Download WordPress

Forums

How to run a left join query on postmeta table? (14 posts)

  1. imjscn
    Member
    Posted 3 years ago #

    Hello, I'm dying for a tip, please help!
    I want to run a query on postmeta table and get 3 custom field value for each post. but my code got the post_id only.

    $query = <<<QUERY
    SELECT      project_id.post_id
    FROM        $wpdb->postmeta project_id
    LEFT JOIN  $wpdb->postmeta height
                on height.post_id = project_id.post_id
                and height.meta_key = 'height'
    LEFT JOIN  $wpdb->postmeta width
                on width.post_id = project_id.post_id
                and width.meta_key = 'width'
    WHERE       project_id.meta_key = 'project_id'
    ORDER BY    project_id.meta_value+(0) ASC
    QUERY;

    If I remove the LEFT JOIN, the code still get the post_id. So, obviously my left join is wrong. Please help!

  2. s_ha_dum
    Member
    Posted 3 years ago #

    You are only asking for project_id.post_id so you are only going to get that post_id. You need to ask for all the fields you want returned.

    SELECT project_id.post_id, height.height, width.width....

  3. imjscn
    Member
    Posted 3 years ago #

    ok,just now I changed the first 2 lines to:

    SELECT project_id.post_id, height.height, width.width
    FROM $wpdb->postmeta project_id,$wpdb->postmeta height,$wpdb->postmeta width

    var_dump returns array(0) { }
    (if don't add height and width table in FROM line, the same 0 return )

    Please give further instruction!

  4. GRAQ
    Member
    Posted 3 years ago #

    Check the error response, you don't need modify line two as you have done. You can already SELECT height.* and width.* from your LEFT JOINs

  5. s_ha_dum
    Member
    Posted 3 years ago #

    Yes. GRAC is correct. You don't need to list your tables like that in the 'FROM' clause. The joins are enough to bring those tables into the query, and you could end up with naming conflicts if you try to do both.

    Also, you should make sure that you are using valid table and column names. I guessed at those values.

    It might help to echo your MYSQL errors, if you aren't already.

  6. imjscn
    Member
    Posted 3 years ago #

    I put the 2 lines in template file, right below the line I echo the query result, it prints nothing.

    <?php $wpdb->show_errors(); ?>
    <?php $wpdb->print_error(); ?>

    I changed the 2nd line of the query to FROM $wpdb->postmeta project_id, still no error messages and returns project_id, This line might be the problem, coz it's the project_id, not contains height and width info.
    Here's how they look like in postmeta table:

    -------------------------------------
    post_id |  meta_key   | meta_value   |
    -------------------------------------
       1    | project_id  |    1         |
    -------------------------------------
       1    |  height     |    50        |
    -------------------------------------
       1    |  width      |    100       |
    -------------------------------------
       2    | project_id  |    2         |
    -------------------------------------
       2    |  height     |    50        |
    -------------------------------------
       2    |  width      |    100       |
    --------------------------------------

    Edit:
    Now I changed the 1st line to

    SELECT  project_id.post_id, project_id.meta_value,project_id.meta_key

    got this:

    array(3) {
      [0]=>
      object(stdClass)#101 (3) {
        ["post_id"]=>
        string(3) "1"
        ["meta_value"]=>
        string(3) "1"
        ["meta_key"]=>
        string(7) "project_id"
      }
      [1]=>{...}
      [2]=>{...}
    }

    Seems, the "FROM $wpdb->postmeta project_id " is wrong.
    But, "FROM $wpdb->postmeta" deosn't give any result.

  7. imjscn
    Member
    Posted 3 years ago #

    New finding- if I change the first 2 lines to:

    SELECT  project_id.post_id, height.meta_value,width.meta_value
    FROM $wpdb->postmeta project_id

    The output will show:

    array(3) {
      [0]=>
      object(stdClass)#101 (3) {
        ["post_id"]=>
        string(3) "1"
        ["meta_value"]=>
        string(3) "50"
      }
      [1]=>{...}
      [2]=>{...}
    }

    After serveral test, I see it can only output 3 items in one object (post_id, meta_key, meta_value)
    It takes whatever value (height,width...)I specific in SELECT line, but each object can only have one post_id, one meta_key and one meta_value.
    So, now the problem is a bit clear-- my code makes the query result structured the same as postmeta table, to get one id with all related height/width..., I need another structure.
    Please help!

  8. imjscn
    Member
    Posted 3 years ago #

    @apljdi,
    Could you help check this code? It gets the correct result but from multi tables, I don't understand what is pd, pa, and u. / p. , hope there's a way I can borrow this code to apply on postmeta table.

    $query = <<<QUERY
    			SELECT 	DISTINCT(u.ID),
    				u.user_email,
    				u.user_nicename,
    				u.display_name,
    				pd.value
    			FROM $wpdb->users u
    			LEFT
    				JOIN {$bp->profile->table_name_data} pd
    				ON u.ID = pd.user_id
    			LEFT
    				JOIN (
    					SELECT DISTINCT( p.post_author ) ID
    					FROM $wpdb->posts p
    					WHERE
    						p.post_type = 'ep_reg'
    						AND p.post_parent = $postid
    						$for_admin
    				) pa
    				ON u.ID = pa.ID
    			WHERE
    				u.user_status = 0
    				AND pa.ID IS NULL
    				AND pd.field_id = 1
    			ORDER BY pd.value ASC
    QUERY;
  9. s_ha_dum
    Member
    Posted 3 years ago #

    pd, pa, and u. / p.

    Those are table 'aliases'. Look at this part of the code:

    FROM $wpdb->users u

    That is the same as, and can also be written as:

    FROM $wpdb->users AS u

    Now, within the query you can refer to the wp_users as 'u'.

  10. imjscn
    Member
    Posted 3 years ago #

    I see.
    Thanks for helping! I will try to see if I can apply this code on postmeta table or not.

  11. imjscn
    Member
    Posted 3 years ago #

    @apljdi
    Seems there's no way to query colums as keys in postmeta table. I decide to move on in other ways.

    Somedays ago, I learned get_post_meta from you. Now I do it this way:

    function mp_all_ids( ){
    global $wpdb, $post;
    $query = <<<QUERY
    SELECT      project_id.post_id
    FROM        $wpdb->postmeta project_id
    WHERE       project_id.meta_key = 'project_id'
    ORDER BY    project_id.meta_value+(0) ASC
    QUERY;
    
    $postids = $wpdb->get_col( $query );
    
    foreach ($postids as $id) {
        $meta=get_post_custom($id);
    
    	$data = Array(
    	'ID' => $meta['project_id'][0],
    	'height' => $meta['height'][0],
    	'width' => $meta['width'][0]
    	);
    }
    return apply_filters( 'mp_all_ids', $data );
    }

    From var_dump($data), this function get only one result(because of Foreach) like this:

    array(3) {
      ["ID"]=>
      string(3) "463"
      ["height"]=>
      string(16) "100"
      ["width"]=>
      string(32) "50"
    }

    How can I get all projects with data like this:

    array(2) {
      [0]=>
      object(stdClass)#1 (3) {
        ["ID"]=>
        string(1) "2"
        ["height"]=>
        string(3) "100"
        ["width"]=>
        string(2) "50"
      }
      [1]=>
      object(stdClass)#2 (3) {
        ["ID"]=>
        string(1) "1"
        ["height"]=>
        string(3) "200"
        ["width"]=>
        string(3) "100"
      }
    }

    Please help!

  12. s_ha_dum
    Member
    Posted 3 years ago #

    $data[] = ...

  13. imjscn
    Member
    Posted 3 years ago #

    Great! Got all projects by $data[] = ...

    Still need one more tip to complete-
    Now, each project is an array, how to get it as object(stdClass){...} ?

    The array comes from this:
    $data[] = Array(...);
    If I change to $data[] = object(...), then , the "=>" is wrong.

  14. imjscn
    Member
    Posted 3 years ago #

    @apljdi,

    Complete! $data[] = (object)Array(....);

    Thanks so much for your helping!

Topic Closed

This topic has been closed to new replies.

About this Topic