Akelos Framework v1 forum archive. This forum is no longer maintained. To report bugs please visit https://github.com/akelos/akelos/issues
    • CommentAuthorsuthern

    My Goal: Get the ID for the NEXT and PREVIOUS from the following list of IDs, in the order they are. (NOT ordered by ID)

    • 4
    • 7
    • 6
    • 2
    • 3
    • 5

    Those are the Table's IDs. They are sorted this way from a combination of other columns. Example: I'm currently at row 6, so I would need IDs 7 and 2. (PREVIOUS = 7, and NEXT = 2). I'm fine with two SQL queries.

    There are two main approaches that I see:

    1. Do it in PHP after using a generic SELECT * FROM table ORDER BY something,something_else;
    2. Do it in database using SQL;

    I've accomplished number one already, but I think that it is really a waste of resources to do a select all, especially on a table that will contain over 4000 items, only to get two numbers from it. Thus I'm seeking an alternative route.

    I tried this code SELECT * FROM spots WHERE id > 6 ORDER BY warehouse_id,section,bin,tray DESC LIMIT 1 But it is incorrect because the WHERE clause limits the ID to one larger than 6, even if the next index is actually a smaller number, like "2". Obviously not a solution. It was, however, the only one I ran across after searching Google for a little while.

    Is this actually possible with SQL? I've looked at sql subqueries a little, but haven't made any progress. Any SQL gurus out there that'd chip in little bit of knowledge? The only thing I can offer you in reward is a 'thank you', or perhaps a self-served pat-on-the-back.


    • CommentAuthorsuthern

    If the above is not possible, could someone point out what function to use to get a SIMPLE result, as in, a simple array? The objects that Akelos makes are great, but not needed in this case.


    • CommentAuthorKaste

    given: a SQL that gets the rows:

    [id]  4, 7, 6, 2, 3, 5
    [idx] 0  1  2  3  4  5

    then you get the row with the ID = 2 with the same SQL plus



    • CommentAuthorsuthern

    Ahh, but I do not know ahead of time what the offset is. All I know is the originating ID. SELECT * FROM spots ORDER BY warehouse_id, section, bin, tray DESC LIMIT 1 OFFSET 3 So my next question obviously is, how do I determine the correct offset given only the originating ID, without selecting all the rows from the database.

    hands free beer to Kaste

    • CommentAuthorsuthern

    Here is my source table, ORDER BY warehouse_id, section, bin, tray DESC . id warehouse_id section bin tray 5 1 A 3 B 4 1 A 9 B 2 1 B 3 Z 6 1 B 3 Z 3 2 A 2 AB 1 2 A 2 A

    For now my quest is to simply return the current "ranking" of the selected ID. I've gathered three different SQL approaches, and all give me different results, none of which are right in all cases. For each query, we'll be using the current ID of "4". Query 1:SELECT count( * ) -1 AS id FROM ( SELECT * FROM spots WHERE id > ( SELECT id FROM spots WHERE id = 4 ) ORDER BY warehouse_id, section, bin, tray DESC ) AS s Result 1: "1"
    Query 2:SELECT COUNT(*) AS id FROM spots s1 JOIN spots s2 ON s1.id>s2.id AND s2.id=4 ORDER BY s2.warehouse_id, s2.section, s2.bin, s2.tray DESC Result 2: "2"
    Query 3: SELECT CONCAT(COUNT(*)+1,'/',(SELECT COUNT(*) FROM spots)) id FROM spots WHERE id > (SELECT id FROM spots WHERE id=4) Result 3: "3/6".

    From ID of 4, it looks like either querys 1 or 2 could be correct. However, if we query with an id of 2, we get

    Query 1:3
    Query 2:4
    Query 3:5/6

    Thus, we see that query 1 is the only one close, and it does not jive with when we did the query for ID 4. If query one is giving us an index starting at 1, and going to 6, then it's result for ID=4 should have been 2, not 1.

    Just thought I'd throw that out there so someone else can puzzle over it too. Hmm, even if I put the ORDER BY warehouse_id, section, bin, tray DESC into the 3rd query, it returns the same result.

    This is getting very curious...

    • CommentAuthorKaste

    I propose a more structured dive.

    we need a rank:

    SELECT @rank:=@rank+1 rank,f.* FROM (select @rank:=0) r, (--PLACE YOUR ORIGINAL QUERY HERE--) f


    SELECT @rank:=@rank+1 rank,s.* FROM (select @rank:=0) r, (SELECT * FROM spots ORDER BY warehouse_id, section, bin, tray DESC) s

    now it's getting tricky.

    for a given ID give me the RANK:

    select ranked.rank rank,spots.* from spots join ( SELECT @rank:=@rank+1 rank,s.* FROM (select @rank:=0) r, (SELECT * FROM spots ORDER BY warehouse_id, section, bin, tray DESC) s ) AS ranked on ranked.id=spots.id where spots.id=2 this leads us to

    given an ID give me three in a row around this id

    select spots.* from spots join ( SELECT @rank:=@rank+1 rank,s.* FROM (select @rank:=0) r, (SELECT * FROM spots ORDER BY warehouse_id, section, bin, tray DESC) s ) as ranked on ranked.id=spots.id join ( SELECT @rank2:=@rank2+1 rank,s.* FROM (select @rank2:=0) r, (SELECT * FROM spots ORDER BY warehouse_id, section, bin, tray DESC) s ) as selector on SELECTOR.ID=2 where ranked.rank BETWEEN selector.rank-1 and selector.rank+1

    I don't know though if one can refactor this last query.

    • CommentAuthorsuthern

    You sir, are a true genius! The only error in your last SQL statement was the CAPS on 'SELECTOR.ID=2'.

    It works great in all my tests, and will save a TON of memory & resources.

    gives two free beer to Kaste

    I do have two more simple related questions:

    • How can I do a simple SQL query and obtain the result without making an object? For instance when I'm doing a UNIQUE/DISTINCT Select statement to build an index of clickable links, and I don't need a huge memory-gobbling object.
    • How can I serialize input for a custom findBySQL command? (like in the above, where I'll be passing params['id'] to the SQL statement)


    • CommentAuthorKaste

    first question:

    // => returns an Iterator so you have to while..do $result->fetchRow()...
    $this->_db->select*(sql);     // * = there are different select-methods selectAll etc...
      sql = 'select * from table where id=2' or
           = array('select * from table where id=?',$id)

    which will return an array, pronto.

    I don't understand the second question.

    • CommentAuthorsuthern

    Ah, that's handy!

    Perhaps 'serialize' is not the best word. What I need to do is protect from SQL injection attacks, so someone can't put "; do random stuff" in the URL and erase the DB or worse.

    If I'm doing a FIND, I can do this to clean my input ($this->params[]) $this->controller->find('all',array('conditions' => array('name = ? lastName = ?',$this->params['name'],$this->params['lastName']))); Which causes the inputs to be cleaned before the SQL statement is creatd. But how I'm not sure how to get a similar cleaning effect if I want to use custom SQL, as in findBySQL(). Is that clearer?

    • CommentAuthorKaste

    for the branch as stated above.

    for the trunk:

    • CommentAuthorsuthern

    Got it.