How to use django-sql-explorer in Tendenci

Tendenci 6 ships with the excellent django-sql-explorer from ePantry. First - 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.

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.

  1. Login to your Tendenci6 site at /accounts/login/
  2. Navigate to /explorer/
  3. Click on playground and test out some queries. For example here are two
    1. "select tablename from pg_tables" - without the quotes to list all 300 tables in your database
    2. "select * from articles_article" - list all articles including expired and inactive etc.
  4. If you like the queries click "new query" and name and describe them and click save.
  5. 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 follows. Logged in as a super user you won't see it linked but here note the new megamenus that are context sensitive similar to the old outdated tabs. After logging in your should see something like this:

 

tendenci6megamenu.png

 

Navigate to /explorer/ by typing it in the url and you should see a page similar to this one when you click on "playground"

django-sql-query-dashboard.png

First note the icon on the right to Download CSV so you can download all of whatever that query is for. Next this is what writing one looks like.

django-sql-playground.png

 

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.

creating-new-tendenci-query-for-csv-download.png

 

 

Here are a few other useful queries for running reports. Just copy and aste them into the SQL area of a new querie:

  

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:



2) ALL memberships:

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 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'



3) ALL corporate members:

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:

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'




4) All users in a specific group (replace <YOUR GROUP ID> with your group id)

SELECT ug.name, 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 FROM auth_user u INNER JOIN profiles_profile p ON u.id=p.user_id INNER JOIN user_groups_groupmembership ugm on u.id=ugm.member_id INNER JOIN user_groups_group ug on ug.id=ugm.group_id WHERE ug.id=<YOUR GROUP ID> AND ugm.status=True AND ugm.status_detail='active'

Copy Paste Version:

SELECT ug.name, 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 FROM auth_user u INNER JOIN profiles_profile p ON u.id=p.user_id INNER JOIN user_groups_groupmembership ugm on u.id=ugm.member_id INNER JOIN user_groups_group ug on ug.id=ugm.group_id WHERE ug.id=<YOUR GROUP ID> AND ugm.status=True AND ugm.status_detail='active'

 

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!

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.