SQL explorer is a way to directly query your site through the user interface. It is for superusers only and we recommend disabling it by default (see disclaimer above.) But if you are still reading here is the lightning version.
- As a super_user navigate to /explorer/
- Click on playground and test out some queries. For example here are two:
- "select tablename from pg_tables" - without the quotes to list all 300 tables in your database.
- "select * from articles_article" - list all articles including expired and inactive etc.
- If you like the queries click "new query" and name and describe them and click save.
- Click on the SQL explorer icon top left and your back at the dashboard with icons to download the results of your queries.
A more detailed version of using sql explorer to gather business intelligence (BI) for your association:
Logged in as a super user. You won't see it linked, but here note the new mega menus that are context sensitive similar to the old outdated tabs. After logging in, you should see something like this:
Navigate to /explorer/ by typing it in the url to this one when you click on "playground". Note the icon on the bottom to Download CSV, Exel or JSON, so you can download all of whatever that query is for. Next this is what writing one looks like. Explore your Schema:
And then when you click "New Query" you will find this interface and you can carefully name and describe your query so you know what it does later. In the example below Tendenci SQL Explorer from Django allows your team to explore the data directly and build ad-hoc queries for the specific needs of your association or organization. The point is "your data is your data" - it's what OPEN is all about.
Example AMS useful queries for running reports. Copy and paste into the SQL area of a new query:
1) ALL Interactive users:
SELECT u.first_name, u.last_name, u.email, u.username, u.is_staff, u.is_superuser, p.salutation, p.company, p.position_title, p.phone, p.address, p.address2, p.member_number, p.city, p.state, p.zipcode, p.country, p.url, p.sex, p.address_type, p.phone2, p.fax, p.work_phone, p.home_phone, p.mobile_phone, p.notes, p.admin_notes FROM auth_user u INNER JOIN profiles_profile p ON u.id=p.user_id WHERE u.is_active=True AND p.status=True AND p.status_detail='active'
Copy Paste Version:
SELECT u.first_name, u.last_name, u.email, u.username, u.is_staff, u.is_superuser, p.salutation, p.company, p.position_title, p.phone, p.address, p.address2, p.member_number, p.city, p.state, p.zipcode, p.country, p.url, p.sex, p.address_type, p.phone2, p.fax, p.work_phone, p.home_phone, p.mobile_phone, m.membership_type_id, m.renewal, m.certifications, m.work_experience, m.referer_url, m.referral_source, m.join_dt, m.expire_dt, m.renew_dt, m.primary_practice, m.how_long_in_practice, m.application_approved, m.application_approved_dt, m.areas_of_expertise, m.home_state, m.year_left_native_country, m.network_sectors, m.networking, m.government_worker, m.government_agency, m.license_number, m.license_state, m.status_detail FROM auth_user u INNER JOIN profiles_profile p ON u.id=p.user_id INNER JOIN memberships_membershipdefault m ON m.user_id=u.id WHERE u.is_active=True AND p.status=True AND m.status_detail <> 'archive'
Copy Paste Version:
SELECT cp.name, cp.address, cp.address2, cp.city, cp.state, cp.zip, cp.country, cp.phone, cp.email, cp.url, cp.number_employees, cp.chapter, cp.tax_exempt, cp.annual_revenue, cp.annual_ad_expenditure, cp.description, cp.expectations, cp.notes, cp.referral_source, cp.ud1, cp.ud2, cp.ud3, cp.ud4, cp.ud5, cp.ud6, cp.ud7, cp.ud8, cm.corporate_membership_type_id, cm.renewal, cm.renew_dt, cm.join_dt, cm.expiration_dt, cm.approved, cm.admin_notes, cm.status_detail FROM corporate_memberships_corpprofile cp INNER JOIN corporate_memberships_corpmembership cm ON cp.id=cm.corp_profile_id WHERE cm.status_detail <> 'archive'
Copy Paste Version:
Have you written some good queries for Tendenci using the amazing django-sql-explorer from ePantry? Post them on the Tendenci Community Site for others to learn and share with!
Note: If you choose to use it you are doing so at your own risk and could possibly corrupt your database beyond repair up to and including requiring a DBA to come in and repair it at a cost of thousands of quid. So... BE CAREFUL. Still, it is empowering and we like knowing our clients can download any of their data when they need it and perhaps contribute back some suggested new reports to the Tendenci Community as a whole.
Happy data-nerd-day and don't get too crazy in there.
Remember the warnings above, it's live data so be careful out there kids.