Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I have a situation where I can't use usual Pod object and I have Advanced Content Type to deal with. I have a very large record set and I need to build a custom query to fetch the attached image records with main record.

Example Data:

Pod Name = coupons

Fields (e.g.) id, name, description, thumbnails(multiple image upload field).

Now, I am querying for example like this:

SELECT t.id, t.name FROM `wp_pods_coupons` t

How do I get the images attached (thumbnails field) with each coupon?

I can't do new Pod('coupons').

share|improve this question

1 Answer 1

up vote 2 down vote accepted
+50

You're right, the attachments are stored within a separate table called wp_podsrel. Within the table you'll find the item_id (coupon id) and the related_item_id (attachment id).

Hence you can get all the related_item_id's as a function of the item_id:

SELECT related_item_id FROM wp_podsrel WHERE item_id = $coupon_id

Your complete query/output may look something like this:

// global $wpdb;
$coupon_id = 1; // custom coupon id
$podsrel = $wpdb->prefix . 'podsrel';

$select_coupon_related_item_ids = $wpdb->prepare( "SELECT related_item_id FROM $podsrel WHERE item_id = %d AND related_item_id NOT IN ( %d )", $coupon_id, 1 );
$coupon_related_item_ids = $wpdb->get_results( $select_coupon_related_item_ids, OBJECT );

foreach ( $coupon_related_item_ids as $coupon_related_item_id ) :
  $coupon_image_url = wp_get_attachment_url( $coupon_related_item_id->related_item_id );
  var_dump( $coupon_image_url );
endforeach; 

Make sure that you're only selecting related attachment id's (and not any other related items)! Feel free to expand the query with your custom field_id if necessary. Or let me know if you need further help! :)

share|improve this answer
1  
I had achieved it using a few joins instead of doing wp_get_attachment_url ultimately. Your answer is perfectly valid. So I am awarding you with the bounty :) –  noc2spam Jun 1 at 13:17
    
Thanks! Glad I could help :) –  Sven Jun 1 at 13:52
2  
Also, if you're using pods.io/docs/code/pods then you can utilize pods.io/docs/code/pods/find and provide a custom 'select', Pods will automatically join tables as you reference field names too. That would be more future proof, as the podsrel table is about to get a facelift for Pods 3.0 when we add support for code-registered relationship fields, storing field names instead of field IDs. –  Scott Kingsley Clark Jun 10 at 14:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.