How to facilitate selection by attributes?

Question

JavaScript

select
t1.value_int y,t2.value_int m,t3.value_int d,
t4.value_string utm_source,
k.value_int kind_int,
i.count cnt
from stat_item_attrs k
left join stat_item i on i.id = k.item_id
inner join stat_item_attrs t1 on t1.item_id = k.item_id and t1.attr_type = 1/*y*/
inner join stat_item_attrs t2 on t2.item_id = k.item_id and t2.attr_type = 2/*m*/
inner join stat_item_attrs t3 on t3.item_id = k.item_id and t3.attr_type = 3/*d*/
inner join stat_item_attrs t4 on t4.item_id = k.item_id and t4.attr_type = 4/*utm_source*/
where
  k.attr_type = 0/*kind*/ and k.value_int in(0,1)/*hosts,leads*/
    and
    t1.value_int = 20 and t2.value_int = 4 and t3.value_int = 4 /*20-04-04*/
    and
    t4.value_string = 'google'
;

There is a certain table with "quantities" - let for a better understanding there will be quantities - HOSTS AND LEADS (stat_item), each entry has attributes (stat_item_attrs) that characterize it (they are in a separate table). Each attribute has a form (type) and a value (uint32 digit). A separate kind attribute characterizes the type of the entry itself in stat_item;
Task: get stat_item records by the values ​​of its attributes.

Above, I gave an example of how to get the quantities of interest by ymd and the value of utm_source.
Question:
1) Is it possible to do without the join heap.
If not: then please calculate approximately the sampling rate (? in the O style ...?) and recommend how to optimize it somehow (I think it will lag very much if there are 20+ attributes)
2) How can the table be transposed?
now it turns out that all quantities will go 1 record = 1 line
It turns out like this:

y m d kind value
20 4 4 0 500
20 4 4 1 10
20 4 3 0 10
20 4 3 1 0

I would like a structure:
y m d kind0 value0 kind1 value1

Answer 1

I'm using
* tFPDF (based on FPDF 1.7)
* Version: 1.24
also need to put the font in a folder next to the file.
Initiation:

$pdf = new PDF();
$pdf->SetLeftMargin(25);
// Add a Unicode font (uses UTF-8)
$pdf->AddFont('DejaVu','','DejaVuSansCondensed.ttf',true);
$pdf->SetFont('DejaVu','',10);
$pdf->AddPage();
.....

But this is generated on the server, that is, a PHP + adjax request, but you didn’t add this tag, sadness and crying cats all over the world ...

spoiler

Answer 2

Are you sure it's a scribble and not a binary? Looks more like a binary, i.e. everything is ok, pdf is there, you just open it with a text viewer. And pdf still doesn't support html, css, etc. you will not get 1 in 1, the only way is html in canvas and then push these pictures into pdf.

Answer 3

select
case when t1.attr_type = 1 and t1.value_int = 20 then t1.value_int end y,
case when t1.attr_type = 2 and t1.value_int = 4  then t1.value_int end m,
case when t1.attr_type = 3 and t1.value_int = 4  then t1.value_int end d,
case when t1.attr_type = 4 and t1.value_string = 'google'  then t1.value_string end utm_source,
k.value_int kind_int,
i.count cnt
from stat_item_attrs k
left join stat_item i on i.id = k.item_id
join stat_item_attrs t1 on t1.item_id = k.item_id and t1.attr_type in (1/*y*/,2/*m*/,3/*d*/4/*utm_source*/) and (t1.value_int in (20, 4 /*20-04-04*/) or t1.value_string = 'google')
where
  k.attr_type = 0/*kind*/ and k.value_int in(0,1)/*hosts,leads*/

You will get a sparse table, you can collect / group it as you like, max, sum ... for example, by the k.item_id field. But I won’t write here, see for yourself, uniqueness and all that. With the same cases, you can select kind0 value0 kind1 value1, and then group them. But it's better to first see the data and not blindly group
Who wrote the select that you sent, there is not the same logic that you wrote under the request. those. it is not clear why you have left Join to the MAIN table according to your description. Why is stat_item_attrs the main one here?
I will not get into the logic, but here it is better to switch to stat_item.id and remove the left join.