Tendenci – The Enterprise Open Source AMS Blog

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.

Visually when you add the URL /explorer/ to the end of your site path you will see something very similar to this.

First note the icon on the right to Download CSV so you can download all of whatever that query is for.  If you don’t see any, no worries – that’s what this post is about!

So let’s write a basic sql statement.

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.

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, 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’

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’

Contribute back your brilliance to the rest of us? 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!

Please do be careful. Remember the warnings above. Using a live sql tool on a relational database for anything besides SELECT queries is ill-advised. It really is your live data on a live site SO BE CAREFUL!

We believe this level of access to the superusers on Tendenci sites is empowering. We like knowing people 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!

Because Tendenci is part of the Django community and we couldn’t have brought this functionality to you without others in the community “giving first.” We may have added it to Tendenci, but that’s ONLY because of the generosity of building and making it available by others. Explorer is brought to you by the power of collaborative open source software (THANK YOU ePantry!)