Zikula Community

• ccandreva
  
  

  • Rank: Developer
  • Registered: Sep 22, 2005
  • Last visit: May 20, 2010
  • Posts: 292

  Posted: Mar 03, 2011 - 05:54 AM

  Is it possible to use COUNT with DBUtil ? Specifically, I want to join a count of rows in another table to the left table. (In my case, I have a family table and a children table, and I want a count of the number of children in each family)
  
  Here is an SQL example I found that counts the number of members in a group.
  

  Code

  SELECT t1.g_name,count(t2.g_id)
      FROM groups t1,users t2
      WHERE t1.g_id = t2.g_id
      GROUP BY t1.g_name;

  
  
  Can this be done with DBUtil::selectExpandedObjectArray ?

  • 
  •  
• craigh
  
  

  • Rank: Developer
  • Registered: Jun 16, 2003
  • Last visit: May 29, 2010
  • Posts: 1967

  Posted: Mar 03, 2011 - 10:10 AM

  I assume you've looked at the various select**Count**() methods?
  
  selectExpandedObjectCount()
  selectObjectCount()
  selectObjectCountByID()

  • 
  •  
• rgasch
  
  

  • Rank: Team Member
  • Registered: Jan 05, 2003
  • Last visit: May 28, 2010
  • Posts: 776

  Posted: Mar 03, 2011 - 09:03 PM

  Code

  selectExpandedObjectCount()

  
  is your friend. And yes, it's possible to join across multiple tables.
  
  Greetings
  R

  • 
  •  
• ccandreva
  
  

  • Rank: Developer
  • Registered: Sep 22, 2005
  • Last visit: May 20, 2010
  • Posts: 292

  Posted: Mar 06, 2011 - 05:39 AM

  Quote

  
  I assume you've looked at the various select**Count**() methods?
  

  
  
  Uhm, no, because they aren't mentioned at all in the Wiki docs, which I'm now painfully aware are incomplete.
  OK, back to the drawing board.
  
  
  
  Edited by ccandreva on Mar 06, 2011 - 12:39 PM.

  • 
  •  
• ccandreva
  
  

  • Rank: Developer
  • Registered: Sep 22, 2005
  • Last visit: May 20, 2010
  • Posts: 292

  Posted: Mar 06, 2011 - 05:53 AM

  I've looked at them, and no they don't do what I need.
  
  What I have is two tables (for a school registration system). One is family/parent information: Lastname, home address, etc. Then there is a student table .Students relate to their family by family ID.
  
  I want to get a report of all families info from the family table that includes a count of the number of children they have in the school. So, each row returned would have fields from the family table, and a column of the count of the number of records in the children table with that family ID.
  
  The SQL I posted above would do it if executed directly. The question is, can it be done through DBUtil ?
  

  Code

  SELECT t1.g_name,count(t2.g_id)
      FROM groups t1,users t2
      WHERE t1.g_id = t2.g_id
      GROUP BY t1.g_name;

  • 
  •  
• rgasch
  
  

  • Rank: Team Member
  • Registered: Jan 05, 2003
  • Last visit: May 28, 2010
  • Posts: 776

  Posted: Mar 06, 2011 - 07:53 PM

  For queries like this, do the following:
  
  

  Code

  $sql = "SELECT t1.g_name, count(t2.g_id) .....";
  $ca = array ('name', 'count');
  $res = DBUtil::executeSQL ($sql);
  $objects = DBUtil::marshallObejcts ($res, $ca);

  
  
  Since the count() function is not modeled through pntables, you have to execute the SQL directly, but DBUtil can take care of the details of object marshalling, etc. for you.
  
  Greetings
  R

  • 
  •  
•  
•  

Users on-line

• AllKnightAccess,
• clclo

This list is based on users active over the last 60 minutes.