Better way to query Mantis DB?

General discussion of Mantis.

Moderators: Developer, Contributor

Post Reply
elmarculino
Posts: 2
Joined: 05 Feb 2016, 13:40

Better way to query Mantis DB?

Post by elmarculino »

Hi,

I need to run a select in Mantis DB.

After many tests my solution was create a temporary table end than run the select:

Code: Select all

CREATE TEMPORARY TABLE IF NOT EXISTS temp_table AS (
	SELECT @bug_id:=b.id as id,
		t.description AS descricao,
		additional_information AS observacoes,
		c.name AS categoria,
		GROUP_CONCAT((CASE field_id WHEN 24 THEN s.value ELSE NULL END)) AS data_inicio,
		GROUP_CONCAT((CASE field_id WHEN 25 THEN s.value ELSE NULL END)) AS data_fim,
		GROUP_CONCAT((CASE field_id WHEN 34 THEN s.value ELSE NULL END)) AS servidor,
		GROUP_CONCAT((CASE field_id WHEN 28 THEN s.value ELSE NULL END)) AS impacto,
		GROUP_CONCAT((CASE field_id WHEN 30 THEN s.value ELSE NULL END)) AS sistema,
		(SELECT DISTINCT CONCAT( '//images//uploads//', diskfile ) FROM mantis_bug_file_table WHERE bug_id = @bug_id ORDER BY id ASC LIMIT 0, 1) AS print1, 
		(SELECT DISTINCT CONCAT( '//images//uploads//', diskfile ) FROM mantis_bug_file_table WHERE bug_id = @bug_id ORDER BY id ASC LIMIT 1, 1) AS print2,
		(SELECT DISTINCT CONCAT( '//images//uploads//', diskfile ) FROM mantis_bug_file_table WHERE bug_id = @bug_id ORDER BY id ASC LIMIT 2, 1) AS print3
	FROM mantis_bug_table b
	INNER JOIN mantis_bug_text_table t ON b.id=t.id
	INNER JOIN mantis_category_table c ON b.category_id=c.id
	INNER JOIN mantis_custom_field_string_table s ON b.id=s.bug_id
	WHERE b.project_id = 1
	GROUP BY b.id 
);

SELECT * FROM temp_table
What is a better way to implement this select in Mantis DB?

Is my query ok?
Post Reply