SitePoint Sponsor

User Tag List

Results 1 to 16 of 16

Hybrid View

  1. #1
    SitePoint Guru Ize's Avatar
    Join Date
    Nov 2005
    Location
    The Netherlands
    Posts
    808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Generate counter field in View

    Hi,

    I want to create a view containing an auto-incremented counter field, based on the order clause.
    Because it's a view, I cannot increment a variable, since it's forbidden to select variables in views.

    Here's my view query so far:

    Code:
    -- view containing all museums tagged 'n8-2009'
    DROP VIEW IF EXISTS museums_2009;
    CREATE VIEW museums_2009 AS
    SELECT
    	Museum.*
    FROM museums Museum
    LEFT JOIN museums_tags MuseumTag ON(Museum.id = MuseumTag.museum_id)
    LEFT JOIN tags Tag ON(MuseumTag.tag_id = Tag.id)
    WHERE Tag.name = 'n8-2009'
    ORDER BY Museum.sort_name ASC;
    I would like to combine this with a simple 1-n counter field, based on the order clause as written in the view.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    while it certainly is possible to produce the number with SQL, this is very, no, make that ~extremely~ inefficient

    you should really try to append the sequential number when your application code loops over the rows in the result set

    p.s. your joins should be INNER JOINs

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru Ize's Avatar
    Join Date
    Nov 2005
    Location
    The Netherlands
    Posts
    808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    while it certainly is possible to produce the number with SQL, this is very, no, make that ~extremely~ inefficient

    you should really try to append the sequential number when your application code loops over the rows in the result set

    p.s. your joins should be INNER JOINs

    I was afraid of that. Thanks for confirming it.
    Adding the number in the application code would be inefficient in itself, as I would be required to always retrieve all the records, even when I only want to display a single one. I'll just add the number field manually to the original table.

    And thanks for the join tip

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Ize View Post
    Adding the number in the application code would be inefficient in itself, as I would be required to always retrieve all the records, even when I only want to display a single one.
    why don't you try explaining what you really want to do? under what circumstances do you want to return only one row by its row number?

    Quote Originally Posted by Ize View Post
    I'll just add the number field manually to the original table.
    that's an even worse idea, since adding a row almost guarantees that you have to resequence the table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru Ize's Avatar
    Join Date
    Nov 2005
    Location
    The Netherlands
    Posts
    808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    that's an even worse idea, since adding a row almost guarantees that you have to resequence the table
    True, that's why I wanted to generate it

    Quote Originally Posted by r937 View Post
    why don't you try explaining what you really want to do? under what circumstances do you want to return only one row by its row number?
    The subject of this project is a festival involving different museums.

    The number actually is a reference to an accompanying booklet to this event. In the booklet museums may reference eachother by number (purely aesthetic, 'cause we don't want to clutter pages with museum names).
    The number is just an index based on alphanumerically sorted position. Were there 26 museums, each starting with a different letter, "A-Museum" would be no. 1, "Z-Museum" would be no. 26.

    On the website, you can view a single museum page, containing info about that particular museum. It'll also show the number. There I wouldn't want to fetch every record for the sole purpose of adding the number.
    There are also pages (lists) showing every museum in the database, and a page showing a map of the city with those numbers explaining the locations of the individual museums. In this case the number could indeed be safely added by the application code.

    Does this help?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i would simply use an auto_increment for this
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru Ize's Avatar
    Join Date
    Nov 2005
    Location
    The Netherlands
    Posts
    808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i would simply use an auto_increment for this
    Yes, but the thing is, for this event, we use a subset of a bigger 'museums' table, because only some museums will participate. So I introduced the above view as a convenience method, because that inner join would be made every time anyway.

    Also, the auto_increment would need reindexing of some sort whenever a new museum is inserted. In this case for instance:

    Code:
    key | name
    1: "AA"
    2: "BB"
    
    insert into museums (name) values ("AB");

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Ize View Post
    Yes, but the thing is, for this event, we use a subset of a bigger 'museums' table, because only some museums will participate.
    for the museums that don't participate, don't show the auto_increment


    Quote Originally Posted by Ize View Post
    Also, the auto_increment would need reindexing of some sort whenever a new museum is inserted.
    only if you wanted the numbers to reflect the alphabetic sequence

    your users would never notice this type of nuance

    i think you're making things harder than they have to be

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru Ize's Avatar
    Join Date
    Nov 2005
    Location
    The Netherlands
    Posts
    808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    for the museums that don't participate, don't show the auto_increment


    only if you wanted the numbers to reflect the alphabetic sequence

    your users would never notice this type of nuance

    i think you're making things harder than they have to be

    Hehe, that might be right, but I actually do want the numbers to reflect the alphabetic sequence. For instance, in the booklet the museums are ordered by name, and will therefore also show a logical increment in the accompanying numbers.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Ize View Post
    Hehe, that might be right, but I actually do want the numbers to reflect the alphabetic sequence. For instance, in the booklet the museums are ordered by name, and will therefore also show a logical increment in the accompanying numbers.
    the web is not a booklet

    have you thought of using something other than a number? how about a 4-character unique acronym?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    well, you have several suggestions to choose from, now all you have to do is pick one

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Guru Ize's Avatar
    Join Date
    Nov 2005
    Location
    The Netherlands
    Posts
    808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    True
    Thanks for thinking along, Rudy, but I don't there's a solution to my problem that's good from a database point-of-view.

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    CREATE VIEW museums_2009 
    AS
    SELECT museums.something
         , museums.anything
         , museums.just_not_the_dreaded_evil_select_star 
         , ( SELECT COUNT(*) + 1
               FROM tags                                      
             INNER                                     
               JOIN museums_tags                       
                 ON museums_tags.tag_id = tags.id      
             INNER                                     
               JOIN museums AS other_museums                            
                 ON other_museums.id = museums_tags.museum_id
              WHERE tags.name = 'n8-2009'              
                AND other_museums.sort_name < museums.sort_name 
           ) AS your_silly_sequential_number
      FROM tags
    INNER
      JOIN museums_tags 
        ON museums_tags.tag_id = tags.id
    INNER
      JOIN museums
        ON museums.id = museums_tags.museum_id
     WHERE tags.name = 'n8-2009'
    ORDER 
        BY museums.sort_name ASC;
    please do not ask me to debug this, nor optimize its performance, nor explain how to use it to do previous/next paging
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Guru Ize's Avatar
    Join Date
    Nov 2005
    Location
    The Netherlands
    Posts
    808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    CREATE VIEW museums_2009 
    AS
    SELECT museums.something
         , museums.anything
         , museums.just_not_the_dreaded_evil_select_star 
         , ( SELECT COUNT(*) + 1
               FROM tags                                      
             INNER                                     
               JOIN museums_tags                       
                 ON museums_tags.tag_id = tags.id      
             INNER                                     
               JOIN museums AS other_museums                            
                 ON other_museums.id = museums_tags.museum_id
              WHERE tags.name = 'n8-2009'              
                AND other_museums.sort_name < museums.sort_name 
           ) AS your_silly_sequential_number
      FROM tags
    INNER
      JOIN museums_tags 
        ON museums_tags.tag_id = tags.id
    INNER
      JOIN museums
        ON museums.id = museums_tags.museum_id
     WHERE tags.name = 'n8-2009'
    ORDER 
        BY museums.sort_name ASC;
    please do not ask me to debug this, nor optimize its performance, nor explain how to use it to do previous/next paging
    I won't
    Let me check its performance and if it's not too bad, I might just use it. Many thanks!

  15. #15
    SitePoint Guru Ize's Avatar
    Join Date
    Nov 2005
    Location
    The Netherlands
    Posts
    808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow! With the current table it only takes 0.79sec (run from MySQL app, not from website, where it probably will be a little slower).
    But for now that's totally acceptable.


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
  •