WordPress.org

Ready to get started?Download WordPress

Forums

Exports and Reports
[resolved] PodsCMS based report (6 posts)

  1. rhysbwaller
    Member
    Posted 3 years 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 3 years 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
    Plugin Author

    Posted 3 years 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 3 years 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
    Plugin Author

    Posted 3 years 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 3 years 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.

About this Plugin

About this Topic