SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict KJedi's Avatar
    Join Date
    Sep 2005
    Location
    Ukraine, Nikolaev
    Posts
    231
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Extensive filtering using groups

    I'm working on a project now where I have to create an extensive filtering. Users belong to a number of groups and I should process requests when user belongs to several groups. Say find users that are both in golf club and in beer club. (see case #3 here)
    There is a solution there too. But I feel it is not optimal. I think there may be something better.

    All ideas/suggestions are welcome. Both here and on the blog
    Thanks for help beforehand!
    Last edited by KJedi; Mar 19, 2010 at 00:41. Reason: topic correction
    IQ RIA - Delivering smart web-applications
    High-quality PHP, Yii, Ruby on Rails, ExtJS, Backbone, EmberJS and jQuery consulting.
    Dashboards, HTML5 and CRM development.
    Request a quote now!

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,892
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    Another query:

    Code SQL:
    SELECT
      u.*
    FROM
      USER u
    INNER JOIN
      UserGroup ug1
    ON
      u.id = ug1.userID AND ug1.groupID=7
    INNER JOIN
      UserGroup ug2
    ON
      u.id = ug2.userID AND ug2.groupID=15

    Uses JOINs instead of subqueries, should be faster. Especially if UserGroup.groupID and UserGroup.userID both have an index.

  3. #3
    SitePoint Addict KJedi's Avatar
    Join Date
    Sep 2005
    Location
    Ukraine, Nikolaev
    Posts
    231
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    COOL!!!! Besides speed it is more simple to form such query!!!
    Thanks!

    Maybe there even more efficient ways of doing this? I'm OK to change DB structure, but I want to preserve the current flexibility.
    IQ RIA - Delivering smart web-applications
    High-quality PHP, Yii, Ruby on Rails, ExtJS, Backbone, EmberJS and jQuery consulting.
    Dashboards, HTML5 and CRM development.
    Request a quote now!

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,892
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by KJedi View Post
    COOL!!!! Besides speed it is more simple to form such query!!!
    Thanks!

    Maybe there even more efficient ways of doing this? I'm OK to change DB structure, but I want to preserve the current flexibility.
    I don't see why you should change the DB structure to perform this query, or am I missing something?

  5. #5
    SitePoint Addict KJedi's Avatar
    Join Date
    Sep 2005
    Location
    Ukraine, Nikolaev
    Posts
    231
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, I meant that I'm OK to redesign DB if needed in order to achieve the same result more efficiently. For example, I know I can store hierarchy more efficiently with additional fields that allow me to get all subtree of a node with the single query.
    I thought that there may be other optimizations similar to this one for my task.
    IQ RIA - Delivering smart web-applications
    High-quality PHP, Yii, Ruby on Rails, ExtJS, Backbone, EmberJS and jQuery consulting.
    Dashboards, HTML5 and CRM development.
    Request a quote now!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •