Get data from WordPress MySQL user_meta table and my custom table

Question

I have a custom table "orders" in which I save orders data.

| id | user_id | product

In this way, I get data from it.

$orders = $wpdb->get_results( 
   "
   SELECT * 
   FROM sbgf_pl_orders
   ORDER BY ID DESC
   LIMIT 500
");

foreach ( $orders as $order ){ 
  echo $order->id;
}

I want to link my request with user_meta table so that I would get orders from those users whose profile has a specific delivery method as suer meta "shippment_method".

I tried this, but this request overloads my site

$orders = $wpdb->get_results( 
   "
   SELECT * 
   FROM sbgf_pl_orders, sbgf_user_meta
   WHERE sbgf_user_meta.shipping_method = '1' AND sbgf_user_meta.user_id=sbgf_user_meta.user_id
   ORDER BY ID DESC
   LIMIT 500
");

foreach ( $orders as $order ){ 
  echo $order->id;
}

Answer 1

You are not joining the tables properly :

AND sbgf_user_meta.user_id=sbgf_user_meta.user_id

This condition will always be true, and your join results in a cartesian product between users and orders having shipping method 1.

Also, you should always use explicit joins instead of old-school, implicit joins, as explained for example in this SO answer.

Finally, it is also a good practice to alias the tables in the query, and to prefix the columns with these aliases. This makes the query easier to read and maintain, and avoid ambiguity when column names conflict.

Your query should probably look like :

SELECT * 
FROM sbgf_pl_orders ord
INNER JOIN sbgf_user_meta usr ON usr.shipping_method = 1 AND usr.user_id = ord.user_id
ORDER BY ord.ID DESC
LIMIT 500

Answer 2

@GMB thank you for the idea. I guessed how to do it right

$orders = $wpdb->get_results( 
            "
            SELECT *
            FROM sbgf_pl_orders ord
            INNER JOIN sbgf_usermeta m1 ON (ord.user_id = m1.user_id AND m1.meta_value = '1'
            ORDER BY ord.ID DESC
            LIMIT 500
            "
          );