Skip to content

Instantly share code, notes, and snippets.

@jonahcoyote
Last active January 8, 2019 00:05
Show Gist options
  • Save jonahcoyote/6dabaca0dc5cd990bc29 to your computer and use it in GitHub Desktop.
Save jonahcoyote/6dabaca0dc5cd990bc29 to your computer and use it in GitHub Desktop.
Sabai Directory Export SQL
/*
Intro:
Here are some export examples you can use to export Sabai directory data. I'm no MySQL expert and there's probably better ways to run these queries but this worked for my purposes. I hope this helps!
Note, this is going to export all columns and you'll need to manually go in and remove columns you don't need in Excel or Numbers or whatever spreadsheet program you have.
Usage:
This is meant to be run in phpMyAdmin. Look at your own table structure and modify as necessary for any custom fields, table prefix, and custom bundle. Custom fields are storedin their own tables and cross linked via entity_id which is the id of a listing.
*/
/*
This example selects all fields for a custom bundle (classifieds), plus some custom fields:
_price
_will_ship
_condition
*/
SELECT *
FROM wp_bqvy2nwrc5_sabai_entity_field_content_body as t1
LEFT JOIN wp_bqvy2nwrc5_sabai_content_post as t2
ON t1.entity_id = t2.post_id
LEFT JOIN wp_bqvy2nwrc5_sabai_entity_field_directory_location as t3
ON t1.entity_id = t3.entity_id
LEFT JOIN wp_bqvy2nwrc5_sabai_entity_field_directory_contact as t4
ON t1.entity_id = t4.entity_id
LEFT JOIN wp_bqvy2nwrc5_sabai_entity_field_field_price as t5
ON t1.entity_id = t5.entity_id
LEFT JOIN wp_bqvy2nwrc5_sabai_entity_field_field_will_ship as t6
ON t1.entity_id = t6.entity_id
LEFT JOIN wp_bqvy2nwrc5_sabai_entity_field_field_condition as t7
ON t1.entity_id = t7.entity_id
WHERE t2.post_entity_bundle_name = 'classifieds_listing'
/*
This example selects all fields for a custom bundle (shops), plus one custom field:
_instructors
*/
SELECT *
FROM wp_bqvy2nwrc5_sabai_entity_field_content_body as t1
LEFT JOIN wp_bqvy2nwrc5_sabai_content_post as t2
ON t1.entity_id = t2.post_id
LEFT JOIN wp_bqvy2nwrc5_sabai_entity_field_directory_location as t3
ON t1.entity_id = t3.entity_id
LEFT JOIN wp_bqvy2nwrc5_sabai_entity_field_directory_contact as t4
ON t1.entity_id = t4.entity_id
LEFT JOIN wp_bqvy2nwrc5_sabai_entity_field_field_cert_instructors as t5
ON t1.entity_id = t5.entity_id
WHERE t2.post_entity_bundle_name = 'shops_listing'
/*
This example selects all fields for a custom bundle (instructors), plus one custom field:
_certifications
*/
SELECT *
FROM wp_bqvy2nwrc5_sabai_entity_field_content_body as t1
LEFT JOIN wp_bqvy2nwrc5_sabai_content_post as t2
ON t1.entity_id = t2.post_id
LEFT JOIN wp_bqvy2nwrc5_sabai_entity_field_directory_location as t3
ON t1.entity_id = t3.entity_id
LEFT JOIN wp_bqvy2nwrc5_sabai_entity_field_directory_contact as t4
ON t1.entity_id = t4.entity_id
LEFT JOIN wp_bqvy2nwrc5_sabai_entity_field_field_certifications as t5
ON t1.entity_id = t5.entity_id
WHERE t2.post_entity_bundle_name = 'instructors_listing'
/*
This example selects all fields for a custom bundle (companies), with no custom fields:
*/
SELECT *
FROM wp_bqvy2nwrc5_sabai_entity_field_content_body as t1
LEFT JOIN wp_bqvy2nwrc5_sabai_content_post as t2
ON t1.entity_id = t2.post_id
LEFT JOIN wp_bqvy2nwrc5_sabai_entity_field_directory_location as t3
ON t1.entity_id = t3.entity_id
LEFT JOIN wp_bqvy2nwrc5_sabai_entity_field_directory_contact as t4
ON t1.entity_id = t4.entity_id
WHERE t2.post_entity_bundle_name = 'companies_listing'
/*
This example selects all fields for a custom bundle (calendar), plus some custom fields:
_event_date
*/
SELECT *
FROM wp_bqvy2nwrc5_sabai_entity_field_content_body as t1
LEFT JOIN wp_bqvy2nwrc5_sabai_content_post as t2
ON t1.entity_id = t2.post_id
LEFT JOIN wp_bqvy2nwrc5_sabai_entity_field_directory_location as t3
ON t1.entity_id = t3.entity_id
LEFT JOIN wp_bqvy2nwrc5_sabai_entity_field_directory_contact as t4
ON t1.entity_id = t4.entity_id
LEFT JOIN wp_bqvy2nwrc5_sabai_entity_field_field_event_date as t5
ON t1.entity_id = t5.entity_id
WHERE t2.post_entity_bundle_name = 'calendar_listing'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment