Way to query all tickets with attachments

[Mitar]

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

[Remy Blank]

Sounds like a good addition to the ticket query system. Note that you can probably already do that with reports.

[anonymous]

How do you do it with reports? will you give an example please?

thanks Harel

[Mitar]

You write an SQL query. ;-)

Reports = old system, using SQL queries. Query system = new inteface, generating SQL queries internally.

[anonymous]

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?

Thanks Harel

[Peter Suter]

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"

[anonymous]

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

[Peter Suter]

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"

[anonymous]

Replying to psuter:

Something like this maybe: […]

It works! many thanks

[Peter Suter]

Possibly this could be provided after #10983.