Opened 12 years ago
Last modified 3 years ago
#10286 new enhancement
Way to query all tickets with attachments
|Reported by:||Mitar||Owned by:|
Trac should provide a way to query all tickets with attachments, or even better, with a given name/description in an attachment. Or maybe even attachment size(s) (individual or combined).
Change History (9)
comment:1 by , 12 years ago
comment:2 by , 11 years ago
How do you do it with reports? will you give an example please?
comment:3 by , 11 years ago
You write an SQL query. ;-)
Reports = old system, using SQL queries. Query system = new inteface, generating SQL queries internally.
comment:4 by , 11 years ago
As far as I see, The attachments are not stored inside the DB, but in a separated directory. Therefore, I don;t know How to write such query / report. Whould you please give ne an example?
comment:5 by , 11 years ago
The attachments themselves are not stored in the DB, but there is some metadata. Try
SELECT id, description, filename, size FROM attachment WHERE type=="ticket"
or, to get direct links to the attachments:
SELECT filename AS id, 'attachment' AS _realm, "ticket" AS _parent_realm, id AS _parent_id, description, size FROM attachment WHERE type=="ticket"
comment:6 by , 11 years ago
thanks, It works! If I may, another question: The query above displays each attachment as a separated result. How can I make it displaying by tickets, i.e. display all files that are related to the same ticket as one result (one row in the results table) Thanks again Harel
follow-up: 8 comment:7 by , 11 years ago
Something like this maybe:
SELECT t.id as id, t.summary as summary, group_concat(a.description,", ") as "file descriptions", group_concat(a.filename,", ") as files FROM attachment a INNER JOIN ticket t ON a.id == t.id WHERE a.type=="ticket" GROUP BY t.id
Or to get one table per ticket with one row per attachment:
SELECT a.filename AS id, "attachment" AS _realm, "ticket" AS _parent_realm, t.id AS _parent_id, a.description as description, a.size as size, "#" || t.id || " " || t.summary as __group__, '../ticket/' || t.id as __grouplink__ FROM attachment a INNER JOIN ticket t ON a.id == t.id WHERE a.type=="ticket"
comment:8 by , 11 years ago
Replying to psuter:
Something like this maybe: […]
It works! many thanks
comment:9 by , 8 years ago
Possibly this could be provided after #10983.
Sounds like a good addition to the ticket query system. Note that you can probably already do that with reports.