WordPress.org

1. rhysbwaller
   Member
   Posted 1 year ago #

   Ive got the basics working, but I'm stuck with accessing PICK values.
   SQL Query
   SELECT SQL_CALC_FOUND_ROWS DISTINCT t.*, p.id AS pod_id, p.created, p.modified FROM wp_pod p INNER JOIN wp_pod_tbl_image t ON t.id = p.tbl_row_id WHERE p.datatype = 1

   (N.B backticks have been removed to avoid formatting clash on the forum)

   Pick Field
   Field Name: card
   Data Type: Related
   Related Table: wp_pod_tbl_card
   Related Field: name
   Related WHERE/ORDER BY SQL: t.id=p.tbl_row_id

   This does not return the card.name

2. rhysbwaller
   Member
   Posted 1 year ago #

   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.

3. Scott Kingsley Clark
   Member
   Posted 1 year ago #

   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.

4. rhysbwaller
   Member
   Posted 1 year ago #

   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.

5. Scott Kingsley Clark
   Member
   Posted 1 year ago #

   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.

6. rhysbwaller
   Member
   Posted 1 year ago #

   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

Topic Closed

This topic has been closed to new replies.