Tendenci latest version ships with the excellent Django-SQL-explorer from ePantry.

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. As a super_user navigate to /explorer/
  2. 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.
  3. If you like the queries click "new query" and name and describe them and click save.
  4. 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 superuser. 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:

tendencimegamenu.png

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:

AMS Database Schema On Your Site 

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.

SQL Read Only Queries of Your DB for SuperUsers Only

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:



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:



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:

 

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.

Also, use SQL Explorer to manage spam

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.

 

Contact us to upgrade to Tendenci

The open source solution chosen by associations around the world.

Want to talk? (281) 497-6567

Sign up for Tendenci - The Open Source AMS

No per user pricing. Unlimited admins.

Demo Now

Have Questions?

Contact us!

Site Search



I agree

Our site saves small pieces of text information (cookies) on your device in order to deliver better experience and for statistical purposes. You can disable the usage of cookies by changing the settings of your browser. By browsing our website without changing the browser settings you grant us permission to store that information on your device. See our Privacy Policy