SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)

    Passing Lists to ActiveRecord Find Method

    In regards to the ActiveRecord I really like the idea of passing a array and having the system automatically embed and bind a list to placeholders.

    PHP Code:
    $user User::find(array(
       
    'id'=>array(1,2,3,4,6)
    )); 
    SQL: SELECT x,y,z FROM users WHERE id IN (?,?,?,?,?)

    However, currently my system is set up to embed the first item in a array directly into the string. All items following will bound. This makes it possible to add relatively advanced filters without much hassle.

    PHP Code:
    $a=
    MusicAlbum::find(
        array(
            
    '(YEAR({this}.release_date) BETWEEN'=>array('? AND ?)',2007,2009)
        )
    ); 
    SQL: SELECT t0.`id` AS t0_id,t0.`artist` AS t0_artist,t0.`title` AS t0_title,t0.`release_date` AS t0_release_date FROM music_albums AS t0 WHERE ( YEAR(t0.release_date) BETWEEN ? AND ?)

    That all works great. However, I would like to somehow integrate a way to pass a array and have it treated as a list. This currently is only possible manually due to the fact that when the filter is a array the first item is embedded.

    PHP Code:
    MusicAlbum::find(
        array(
            
    '(id IN'=>array('(?,?,?,?))',2,5,8,67)
        )
    ); 
    SQL:SELECT t0.`id` AS t0_id,t0.`artist` AS t0_artist,t0.`title` AS t0_title,t0.`release_date` AS t0_release_date FROM music_albums AS t0 WHERE ( t0.id IN (?,?,?,?))

    Which works like I said but, I would like to be able to achieve a syntax similar to the following:

    PHP Code:
    $a=
    MusicAlbum::find(
        array(
            
    'id'=>array(2,5,8,67)
        )
    ); 
    Currently that will just result in a valid but unintended SQL statement:

    SELECT t0.`id` AS t0_id,t0.`artist` AS t0_artist,t0.`title` AS t0_title,t0.`release_date` AS t0_release_date FROM music_albums AS t0 WHERE t0.id = 2

    So I was thinking the best way to do this would be that if the field(key) id is primary key or foreign key and the value is a array to override embedding the first item and instead build a list. However, this seems little to restrictive in my opinion and begins to take away a level of control.

    The other way I was thinking is that if the first item in the array is not a string then to default to a list. Which, would work perfectly. However, on the rare case that someone where to pass a list of strings this wouldn't work. Now, I guess that might be rare and there are obvious alternatives yet I would like a solution that works for all datatypes.

    PHP Code:
    User::find(
        array(
            
    'name'=>array('oddz','jfugh','ghfj','fghsj')
        )
    ); 
    In that case the previous solution would fail. oddz would be embedded and the rest bound resulting in invalid sql.

    I would like to keep this as simple and straightforward as possible. This is why nested lists and such are not supported in filters and I rather keep it that way.

    Anyone else have ideas regarding accomplishing this syntax given the listed restraints of how the filters work within the system?

  2. #2
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Instead of using arrays, you could use objects:

    PHP Code:
    User::find(
      array(
        
    'id' => new SqlExpressionIn(array(25867)))); 
    If you create wrappers for the constructors, you can get a more fluent interface:

    PHP Code:
    User::find(
      array(
        
    'id' => sql_in(array(25867)))); 
    I would probably prefer it the other way around - Let the atypical/custom SQL be wrapped in objects, and the rest just be primitives. So:

    PHP Code:
    MusicAlbum::find(
      array(
        
    sql_expression('YEAR({this}.release_date) BETWEEN ? AND ?'20072009))); 
    which would let the simpler case just be:
    PHP Code:
    User::find(
      array(
        
    'id' => array(25867))); 
    An alternative approach could be to have the rule that indexed positions in the arguments array gets treated differently from named positions. You could then do:
    PHP Code:
    MusicAlbum::find(
      array(
        array(
    'YEAR({this}.release_date) BETWEEN ? AND ?'20072009))); 

  3. #3
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    Anyone else have ideas regarding accomplishing this syntax given the listed restraints of how the filters work within the system?
    Yes

    PHP Code:
        MusicAlbum::find($release_date >= dt('2007-01-01') AND $release_date dt('2010-01-01')); 
    but unfortunately the requirements for making that work are exotic, (pecl/operator).

    See http://www.sitepoint.com/forums/showthread.php?t=356259 (Has it been 3 years since I wrote that!? )

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    Seems incredibly complicated for such a relatively simply task. I'm trying to keep the interface as simple as possible.

    Quote Originally Posted by kyberfabrikken
    I would probably prefer it the other way around - Let the atypical/custom SQL be wrapped in objects, and the rest just be primitives. So:

    PHP Code:
    MusicAlbum::find(
      array(
        
    sql_expression('YEAR({this}.release_date) BETWEEN ? AND ?'20072009))); 
    That won't work because the key needs to be isolated. This is done is that the system can determine whether the key is a field or or not. I guess I could just make a second argument and bring them together, but again doesn't seem very simple. I'm trying to keep objects like this out of the equation.

    Quote Originally Posted by kyberfabrikken
    If you create wrappers for the constructors, you can get a more fluent interface:

    PHP Code:
    User::find(
      array(
        
    'id' => new SqlExpressionIn(array(25867)))); 
    I guess if I must use a object then that would work. Again though… not simple.

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    To not break current functionality I'm thinking the best way to do this would be the below.

    PHP Code:
    MusicAlbum::find(

        array(

            
    'id'=>new ActiveRecordList(2,5,8,67)

        )

    ); 
    The list class could then implement a already existing interface called IActiveRecordList which is a linked list.

    Then the where clause class could check if the filter is a instance of IActiveRecordList and handle that problem separately.

    This would also make it possible to write custom lists and I could probably even extend this to the default filter because they are lists as well not that I would need to.

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    This seems incredibly hackish but would work:

    PHP Code:
    MusicAlbum::find(
        array(
            
    'id'=>array(false,4,5,8,67)
        )
    ); 
    Although in theory the first item being false is correct because it doesn't exist. So perhaps null instead.

    PHP Code:
    MusicAlbum::find(
        array(
            
    'id'=>array(null,4,5,8,67)
        )
    ); 

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    I actually think the object is the best solution to this. Instead of having it be a linked list perhaps just implements arrayaccess,countable,iteratorAggregate. However, the naming the class ActiveRecordList goes against what a list is within the system a linked list. Maybe ActiveRecordArray? ActiveRecordCollection already exists also and that is a collection of active record objects.

    PHP Code:
    <?php
    interface IActiveRecordList {
        
        public function 
    addSibling(IActiveRecordList $pItem); // void
        
    public function getSibling(); // IActiveRecordList
        
    public function setSibling(IActiveRecordList $pItem); // void
        
    public function hasSibling(); // boolean

    }
    ?>
    That really just over complicates everything so isn't needed in this instance. So I don't think it would be right to name this array/list as ActiveRecordList. ActiveRecordList does seem like a good name for this though.

  8. #8
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    I could actually have ActiveRecordCollection extend ActiveRecordList then and make it possible to pass a ActiveRecordCollection as a filter also. In that case perhaps the primary keys of the items within that collection are used in the condition perhaps.

    PHP Code:
    MusicTrack::find(
        array(
            
    'album_id'=>MusicAlbum::find(array('id'=>3))
        )
    ); 
    Sorta useless in that circumstance but if a collection object already exists in memory it might be useful.

  9. #9
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    This seems like the best solution.

    PHP Code:
    class ActiveRecordList implements arrayaccess,Countable,iteratorAggregate {


    }

    class 
    ActiveRecordCollection extends ActiveRecordList implements ActiveRecordSavable,ActiveRecordDeletable,IActiveRecordXML {



  10. #10
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    In the end after playing around with this everything seems like a hack. I don't think this is going to make or break the system so I'm just going to leave it out. The less exceptions there are to the general functionality the easier it will be to understand. This isn't worth a exception in my opinion.

  11. #11
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    I haven't been able to put this aside.

    So I was thinking that if the operator matched NOT IN or IN that those keywords would enact the array to be used as a list. So that the first item is not embedded.

    PHP Code:
    $a MusicAlbum::find(
        array(
            
    'id IN'=>array(2,3,4,5,5)
        )
    ); 
    This also makes it possible to use the shorthand in the inverse syntax:

    PHP Code:
    $a MusicAlbum::find(
        array(
            
    'YEAR({this}.release_date) NOT IN'=>array(2004,2006)
        )
    ); 

  12. #12
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    However, if the first item in the array isn't a string then the operand will default to IN and the array will be treated as a list.

    PHP Code:
    $a MusicAlbum::find(
        array(
            
    'id'=>array(5,45,67,89,23,45,12,34)
        )
    ); 
    Code SQL:
    SELECT 
         t0.`id` AS t0_id
         ,t0.`artist` AS t0_artist
         ,t0.`title` AS t0_title
         ,t0.`release_date` AS t0_release_date 
      FROM 
          music_albums AS t0 
     WHERE 
          t0.id IN (?,?,?,?,?,?,?,?)

    This solution seems reasonable I think.


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
  •