Generate counter field in View

[Ize]

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:

-- 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.

[r937]

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

[Ize]

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

[r937]

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?

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

[Ize]

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

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?

[r937]

i would simply use an auto_increment for this

[Ize]

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:

key | name
1: "AA"
2: "BB"

insert into museums (name) values ("AB");

[r937]

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

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

[Ize]

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.

[r937]

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?

[Ize]

the web is not a booklet

have you thought of using something other than a number? how about a 4-character unique acronym?

Hehe, you're right
But add to that that we also provide "previous museum" / "next museum" navigation, and you'll understand that non-sequential numbers would not go unnoticed

An acronym won't fly. The art directors have spoken.
(Again, the number is mostly an aesthetic thing)

[r937]

well, you have several suggestions to choose from, now all you have to do is pick one

[Ize]

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.

[r937]

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

[Ize]

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!

[Ize]

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.