|
ICLE
|
December 14, 2020 at 9:16 am
Attempting to write a report page (Using Ninjatables pro) and need to use the venue slug values in a column.
|
|
Tony
|
December 15, 2020 at 5:16 am
Hi there,
No, there is no shortcode to pull the Venue slug.
Does NinjaTables Pro allow you to hook into each column’s data via code?
|
|
ICLE
|
December 15, 2020 at 7:34 am
Ninjatables Pro appears to need as a Field Value something called a “type post meta key”
|
|
Tony
|
December 15, 2020 at 8:20 am
Venue data isn’t stored in post meta so there isn’t a key to pull that data.
If there isn’t a way to hook into the column I don’t think you’ll be able to pull the venue information into the output.
|
|
ICLE
|
December 16, 2020 at 1:51 pm
If I were to call out the Venue slug value using a MySQL command what would it look like? I have had success getting a report in NinjaTables with this code:
SELECT
evtID as “Event ID”,
eventName as “Event Name”,
eventDate as “Event Date”,
SUM(registrationCount) as “Total”,
SUM(CASE WHEN DATE_FORMAT(registrationDate, ‘%d’) = 01 THEN registrationCount ELSE NULL END) as “01”,
SUM(CASE WHEN DATE_FORMAT(registrationDate, ‘%d’) = 02 THEN registrationCount ELSE NULL END) as “02”,
SUM(CASE WHEN DATE_FORMAT(registrationDate, ‘%d’) = 03 THEN registrationCount ELSE NULL END) as “03”,
SUM(CASE WHEN DATE_FORMAT(registrationDate, ‘%d’) = 04 THEN registrationCount ELSE NULL END) as “04”,
SUM(CASE WHEN DATE_FORMAT(registrationDate, ‘%d’) = 05 THEN registrationCount ELSE NULL END) as “05”,
SUM(CASE WHEN DATE_FORMAT(registrationDate, ‘%d’) = 06 THEN registrationCount ELSE NULL END) as “06”,
SUM(CASE WHEN DATE_FORMAT(registrationDate, ‘%d’) = 07 THEN registrationCount ELSE NULL END) as “07”,
SUM(CASE WHEN DATE_FORMAT(registrationDate, ‘%d’) = 08 THEN registrationCount ELSE NULL END) as “08”,
SUM(CASE WHEN DATE_FORMAT(registrationDate, ‘%d’) = 09 THEN registrationCount ELSE NULL END) as “09”,
SUM(CASE WHEN DATE_FORMAT(registrationDate, ‘%d’) = 10 THEN registrationCount ELSE NULL END) as “10”,
SUM(CASE WHEN DATE_FORMAT(registrationDate, ‘%d’) = 11 THEN registrationCount ELSE NULL END) as “11”,
SUM(CASE WHEN DATE_FORMAT(registrationDate, ‘%d’) = 12 THEN registrationCount ELSE NULL END) as “12”,
SUM(CASE WHEN DATE_FORMAT(registrationDate, ‘%d’) = 13 THEN registrationCount ELSE NULL END) as “13”,
SUM(CASE WHEN DATE_FORMAT(registrationDate, ‘%d’) = 14 THEN registrationCount ELSE NULL END) as “14”,
SUM(CASE WHEN DATE_FORMAT(registrationDate, ‘%d’) = 15 THEN registrationCount ELSE NULL END) as “15”,
SUM(CASE WHEN DATE_FORMAT(registrationDate, ‘%d’) = 16 THEN registrationCount ELSE NULL END) as “16”,
SUM(CASE WHEN DATE_FORMAT(registrationDate, ‘%d’) = 17 THEN registrationCount ELSE NULL END) as “17”,
SUM(CASE WHEN DATE_FORMAT(registrationDate, ‘%d’) = 18 THEN registrationCount ELSE NULL END) as “18”,
SUM(CASE WHEN DATE_FORMAT(registrationDate, ‘%d’) = 19 THEN registrationCount ELSE NULL END) as “19”,
SUM(CASE WHEN DATE_FORMAT(registrationDate, ‘%d’) = 20 THEN registrationCount ELSE NULL END) as “20”,
SUM(CASE WHEN DATE_FORMAT(registrationDate, ‘%d’) = 21 THEN registrationCount ELSE NULL END) as “21”,
SUM(CASE WHEN DATE_FORMAT(registrationDate, ‘%d’) = 22 THEN registrationCount ELSE NULL END) as “22”,
SUM(CASE WHEN DATE_FORMAT(registrationDate, ‘%d’) = 23 THEN registrationCount ELSE NULL END) as “23”,
SUM(CASE WHEN DATE_FORMAT(registrationDate, ‘%d’) = 24 THEN registrationCount ELSE NULL END) as “24”,
SUM(CASE WHEN DATE_FORMAT(registrationDate, ‘%d’) = 25 THEN registrationCount ELSE NULL END) as “25”,
SUM(CASE WHEN DATE_FORMAT(registrationDate, ‘%d’) = 26 THEN registrationCount ELSE NULL END) as “26”,
SUM(CASE WHEN DATE_FORMAT(registrationDate, ‘%d’) = 27 THEN registrationCount ELSE NULL END) as “27”,
SUM(CASE WHEN DATE_FORMAT(registrationDate, ‘%d’) = 28 THEN registrationCount ELSE NULL END) as “28”,
SUM(CASE WHEN DATE_FORMAT(registrationDate, ‘%d’) = 29 THEN registrationCount ELSE NULL END) as “29”,
SUM(CASE WHEN DATE_FORMAT(registrationDate, ‘%d’) = 30 THEN registrationCount ELSE NULL END) as “30”,
SUM(CASE WHEN DATE_FORMAT(registrationDate, ‘%d’) = 31 THEN registrationCount ELSE NULL END) as “31”
FROM
(SELECT
_X1A_esp_registration.EVT_ID as evtID,
_X1A_posts.post_title as eventName,
DATE_FORMAT(_X1A_esp_datetime.DTT_EVT_START, “%m-%d-%Y”) as eventDate,
_X1A_esp_registration.REG_date as registrationDate,
COUNT(_X1A_esp_registration.REG_ID) as registrationCount
FROM _X1A_esp_registration
INNER JOIN _X1A_posts
ON _X1A_esp_registration.EVT_ID = _X1A_posts.ID
INNER JOIN _X1A_esp_datetime
ON _X1A_esp_registration.EVT_ID = _X1A_esp_datetime.EVT_ID
WHERE 1=1 AND DATE_FORMAT(_X1A_esp_registration.REG_date, “%Y%M”) = DATE_FORMAT(CURRENT_DATE, “%Y%M”)
GROUP BY _X1A_esp_registration.EVT_ID, DATE_FORMAT(_X1A_esp_registration.REG_date, “%Y%M%D”)
ORDER BY _X1A_esp_registration.EVT_ID, _X1A_esp_registration.REG_date) as tmp
WHERE 1=1
GROUP BY evtID
|
|
Tony
|
December 17, 2020 at 10:13 am
If I were to call out the Venue slug value using a MySQL command what would it look like?
Working through raw SQL is not something we can do on the forums.
I can tell you that Venues are a custom post type within the posts table and the relationship between Events and Venues is stored within esp_event_venue so you’ll need to join that that able on the EVT_ID, using the VNU_ID value to pull additional values.
Surely the plugin allows you to hook in and run code on the results returned here? I’ve not used Ninja Tables, but threads like this suggest it does:
https://heera.it/ninja-tables-dynamic-data-filter
If you can hook into the data you can pull the venue data easier using the Event ID and our models, then include the details in the column.
|
