EDIT
I’ve picked up that the ‘Related WHERE/ORDER BY SQL’ was missing the ‘WHERE’, so am now working with ‘WHERE t.id=p.tbl_row_id’ but its still not working.
When building Pods queries for reports, I find it’s best to build my $pods->findRecords in PHP, then after running $pods->findRecords do an echo of $pods->sql to see the SQL built by Pods.
I then take that SQL, replace the prefixes used with the real prefix of my db, then I slap it into a report and modify from there as needed.
Thats what I did, and am getting the results, its just accessing the PICK data that is my issue. I have created ‘related’ data type fields and used details like the example I listed above. The columns in the report are blank for PICK fields.
Ah, related data type doesn’t work the same as Pods has an inbetween table for relationships, you’ll need to do your own joining in your SQL query and select the field data you want for the report.
I’m just posting the summary for future reference:
This will create the basis of the sql query:
<?php
$pods = new Pod('image');
$params = array(
'orderby' => 'card.id',
'limit' => -1
);
$pods->findRecords($params);
global $wpdb;
echo str_replace('@wp_', $wpdb->prefix, $pods->sql);
Then add the card to the SELECT to produce the final sql query for the report
SELECT SQL_CALC_FOUND_ROWS DISTINCT t.*, p.id AS pod_id, p.created, p.modified, card.id AS card_id FROM wp_pod p LEFT JOIN wp_pod_rel r1 ON r1.field_id = 82 AND r1.pod_id = p.id LEFT JOIN wp_pod_tbl_card card ON card.id = r1.tbl_row_id INNER JOIN wp_pod_tbl_image t ON t.id = p.tbl_row_id WHERE p.datatype = 1 ORDER BY t.name ASC
Pick Field
Field Name: card_id
Data Type: text
Real Field: card.id
