Okay
  Public Ticket #2114397
wpDataTables and Relational Post Type Column
Closed

Comments

  • R\West Interactive started the conversation

    Hello, I am trying to use wpDataTables to create several tables that display the metadata for a few custom post types I have created in Wordpress. I have been able to create these tables using the GUI for querying the WP database, and most of the values are displaying as expected - however, I am having an issue correctly displaying the meta value of a table column that references a relational custom post type.

    To clarify:

    I have two custom post types set up in Wordpress: Companies and Contacts. Each post type has their own meta fields. For example, a Company post type contains a company name, address, etc, but it also contains a column of relational Contacts (the other post type), which have a meta name value. Each company has 1 or more contact post types associated with it. This is set up as a relational post object field. 

    When I try to add the company contacts to the companies table, the table is pulling in a string value that looks something like this: a:1:{i:0;s:1:”9”;}. I would like this column to instead pull in the contact name, which is a meta value in the contacts post type.

    Below is the SQL query for pulling in the company name, ID, and contacts. I assume that I need to make a change in the section that pulls in the companies_meta_companies_contacts to pull in the contact name (contacts_meta_contacts_first_name). Please let me know how I might achieve this!

    SELECT companies_meta_company_name_tbl.meta_value AS companies_meta_company_name,

           posts_companies.ID AS companies_ID,

           companies_meta_company_contacts_tbl.meta_value AS companies_meta_company_contacts

    FROM wp_posts AS posts_companies

      INNER JOIN (SELECT companies_meta_company_name_tbl_posts.ID as id, meta_value, meta_key  FROM wp_postmeta AS companies_meta_company_name_tbl_postmeta  INNER JOIN wp_posts AS companies_meta_company_name_tbl_posts   ON companies_meta_company_name_tbl_postmeta.post_id = companies_meta_company_name_tbl_posts.ID   AND companies_meta_company_name_tbl_posts.post_type = 'companies') AS companies_meta_company_name_tbl

         ON companies_meta_company_name_tbl.meta_key = 'company_name' AND companies_meta_company_name_tbl.id = posts_companies.ID 

      INNER JOIN (SELECT companies_meta_company_contacts_tbl_posts.ID as id, meta_value, meta_key  FROM wp_postmeta AS companies_meta_company_contacts_tbl_postmeta  INNER JOIN wp_posts AS companies_meta_company_contacts_tbl_posts   ON companies_meta_company_contacts_tbl_postmeta.post_id = companies_meta_company_contacts_tbl_posts.ID   AND companies_meta_company_contacts_tbl_posts.post_type = 'companies') AS companies_meta_company_contacts_tbl

         ON companies_meta_company_contacts_tbl.meta_key = 'company_contacts' AND companies_meta_company_contacts_tbl.id = posts_companies.ID 

    WHERE 1=1 

       AND posts_companies.post_type = 'companies'

  • R\West Interactive replied

    As a side note, I have also tried setting up two separate wpDataTables - 1 for companies and 1 for contacts, and then configuring the contacts column in the company table to reference the contact name from the contact table. However, when I try applying this configuration, I am getting an error and the table will not save. 

  •  404
    Milan replied

    Hi R\West Interactive,
    Thank you for your purchase.

    Please note that this tool is not an ultimate query generator, it just constructs a suggestion of a query. We constantly work on improving it, but SQL is such a complicated and flexible language that full automation for constructing queries is hardly possible. So the more complicated the query is, the higher is the chance that it will not return exactly what you need.

    You can provide me a temporary WP-admin login for your site where this happens, so we could log in and check what is going on. We do not interfere with any data or anything else except for the plugin (in case that’s a production version of the site), and of course we do not provide login data to third party. You can write credentials here just check Private Reply so nobody can see them except us.

    Please note that standard item support does not include:

    Customization services; Installation services; Writing MySQL queries; Writing advanced CSS; Resolving and debugging third party software and hardware issues; Preparing files and tables; Skype, Teamviewer calls or etc. 

    Best regards.

    Check out our documentation for Amelia - WordPress booking plugin on this link.

    Check out our documentation for wpDataTables - WordPress table plugin on this link.

    Kind Regards,
    MIlan Jovanovic
    milan.jovanovic@touchmesoft.net

  •   R\West Interactive replied privately
  •   Milan replied privately
  •   R\West Interactive replied privately
  •  404
    Milan replied

    Hi Taylor,

    You are welcome.

    If your MySQL-query based wpDataTable doesn’t work correctly with server-side processing, probably this is happening because wpDataTables server has problems with parsing of the query and building new queries dynamically (rarely happens, but does sometimes). To avoid this please prepare a MySQL view (a stored query), which will return the data that you need, call it e.g. “view1” and then build a wpDataTabled based on a simple query like “SELECT * FROM view1″.

    Best regards.

    Check out our documentation for Amelia - WordPress booking plugin on this link.

    Check out our documentation for wpDataTables - WordPress table plugin on this link.

    Kind Regards,
    MIlan Jovanovic
    milan.jovanovic@touchmesoft.net

  •   R\West Interactive replied privately
  •  293
    Aleksandar replied

    Hello Taylor.

    Sorry for the late reply.

    Our company is located in Serbia and our working time is from 10:00 to 17:00 CET, business days.

    Please note that the query constructor is not an ultimate query generator. It simply constructs a suggestion of a query. We are constantly working to improve it, but SQL is such a complicated and flexible language that full automation for constructing queries is next to impossible. Consequently, the more complicated the query, the higher is the probability that it will not return exactly what you need. So, you will often need to play around with the resulting query.

    This is why you're getting issues with filtering.

    I assume it added a lot of JOINS and/or CONCAT functions, and with it, the plugin has difficulties locating the data from other tables within the query.

    You'd need to either simplify the query, or use the same one in the database and create a VIEW, like Milan explained. Creating a view is not that difficult, and you don't need to be a developer to do it. Simply type in

    CREATE VIEW view1 AS SELECT (the query generated by wpDataTables query constructor)

    It will create a view which you can pull with:

    SELECT * FROM view1

    Also, please note these limitations on editing tables with wpDataTables may apply:

    • Only one MySQL table can be edited at a time. Queries from multiple tables with joins cannot be used as an editable feature, since SQL UPDATE and INSERT statements are generated automatically, and there currently is no way to update multiple tables – but this problem is being investigated and pursued.
    • Only MySQL tables or tables created with “Create a table manually” and “Create a table by importing data from data source” options of “Create a Table” page can be edited.
    • Server-side processing MUST be turned on for front-end editing – Note: It will turn on automatically if you forget to enable it.
    • MySQL Views cannot be edited properly – only one of the underlying tables can be edited if you pass the proper ID.

    Best regards.

    Check out our new Support Help Center!

    Check out our newest product - Amelia - Enterprise-Level WordPress Appointment Booking Plugin

    Kind Regards, 
    Aleksandar Vukovic
    aleksandar.vukovic@tms-outsource.com