Subject Browse Plugin

I'm looking to extend Omeka with a subject browse - basically, the plugin will get a list of every subject attached to any item in the Omeka instance, and then serve an Alphabetized page of links to searches for these subjects.

My problem isn't that I don't know how to do it - my problem is that I don't know how to do it performantly. Going through the item helpers in the API, this scales very, very badly. But I'm unable to find where subjects are located in the database, if they are in fact located there.

Is there any documentation that would help me figure out how to get a distinct list of all the subjects in the Omeka instance? Or is this data not held in the database, and if so, where does it live?

Fetching results directly from MySQL is very fast:

SELECT DISTINCT et.text
FROM omeka_element_texts et
JOIN omeka_elements e
ON et.element_id = e.id
WHERE e.name = 'Subject'
AND e.element_set_id = (
    SELECT id
    FROM omeka_element_sets es
    WHERE es.name = 'Dublin Core'
)

Thank you. I'd actually figured it out, but forgot to post as such.

Is there any set of conventions/documentation on best practices for using database code in plugins? I'd ideally like to produce a plugin that was reusable (and of good enough quality that people WANT to reuse it ;-), and I want to avoid stepping on metaphorical toes.

If not, I'll just try and be clean and well documented ;-)

There's not really a lot of formal documentation about this (something we need to deal with). There Best Practices page includes some information about creating and using your own tables, but not a lot about selecting.

As for making SQL selects yourself like Jim posted above, that's a perfectly fine thing to do, especially if you already have a query that does exactly what you want.

One tip I can think of at the moment is: Instead of manually specifying table names in your SQL, use (for example) $db->Element and $db->ElementText. This (works with any model) will return the name of the associated table, and automatically include the correct prefix (not everyone's tables start with omeka_).

I encourage you to post on the dev list with general or specific questions about writing a plugin, or even to just share your code for others to look at.

Is anyone building a plugin to support this?

The original poster on this thread recently posted about their plugin on the dev list.

Thanks, John.