## mysql – Dynamic pivot table filtering and performance

I have this those tables and i’m trying to rotate the subscriber to column table to horzental and filter it’s result based on multiple AND/OR conditions like following

    WHERE  first_name LIKE 'm%' AND email LIKE '%com'


This is the fiddle
http://sqlfiddle.com/#!9/a7211d/1

Those are my 2 tables

Fields Table
+----+------------+
| id |label       |
+----+------------+
|  1 | email      |
|  2 | first_name |
|  3 | last_name  |
+-----------------+

Subscribers Fields Table
+----+--------------+----------+---------------+-------------------+
| id | mail_list_id | field_id | subscriber_id | value             |
+----+--------------+----------+---------------+-------------------+
|  1 |            1 |        1 |             1 | mark@examble.com  |
|  2 |            1 |        2 |             1 | Mark              |
|  3 |            1 |        3 |             1 | Wood              |
|  4 |            1 |        1 |             2 | luan@domain.com   |
|  3 |            1 |        2 |             2 | Luan              |
|  4 |            1 |        3 |             2 | Charles           |
|  5 |            1 |        1 |             3 | marry@domain.com  |
|  6 |            1 |        2 |             3 | Anna              |
|  7 |            1 |        3 |             3 | Marry             |
|  8 |            2 |        1 |             4 | kevin@domain.com  |
|  9 |            2 |        2 |             4 | Kevin             |
| 10 |            2 |        3 |             4 | Faustino          |
| 11 |            2 |        1 |             5 | frank@examble.com |
| 12 |            2 |        2 |             5 | Frank             |
| 13 |            2 |        3 |             5 | Denis             |
| 14 |            2 |        1 |             6 | max@example.com   |
| 15 |            2 |        2 |             6 | Max               |
| 16 |            2 |        3 |             6 | Ryan              |
+----+--------------+----------+---------------+-------------------+


This is what i tried but that caused to issues that the email and first_name return 0 instead of value and also it dont work with AND condition operator

select
subscriber_id,
MAX(case when field_id = '1' then value else 0 end) as email,
MAX(case when field_id = '2' then value else 0 end) as first_name,
MAX(case when field_id = '3' then value else 0 end) as last_name
from test_fields_table
WHERE (field_id = 3 AND value LIKE 'm%') OR (field_id = 1 AND value = '%com')
group by subscriber_id limit 100;


However if i removed the Where condition the query works fine with good performance

I also tried to add my query in a subquery give it an alias and then search that generated virtual table using the alias field name instead of the field id but in this case i will have to remove the limit parameter from the subquery in order to be able to search for the full table not just in the first 100 records which cause a very bad performance since this table will be too large 100-500 milion record and i need to get the query result in under 4 seconds.

## dynamic programing. divide boxes stacking to floors

Consider the next problem: in a building, there are boxes in storing on $$n$$ floors for $$1leq i leq n$$ there are $$c_i$$ boxes.
workers required to move the boxes to $$mleq n$$ floors the workers can move boxes from higher floor lower floor.

solution: partition of $${1…n}$$ to $$P_1 …P_m$$ and $$l_1 … l_m$$ the floors that will contain the boxes

define: the cost of transform boxes from floor $$i$$ to floor $$j$$ and $$j leq i$$ is $$c_i(i-j)$$

define: the cost of a solution is $$Sigma_{i=1}^{m} Sigma_{jin F_i} c_j(j-l_i)$$

optimal solution: a legal solution with minimum cost.

for an example: the for an input $$A = {4,2,3,5,8}$$ and $$m=2$$ optimal solution is $$F_1 ={1,2,3} ,F_2 ={4,5}$$ and the floors are: $$l_1 =1 ,l_2 =4$$ the cost of the solution is 16

Find an optimal structure.

my try:
$$OPT(n,m) = begin{cases} Sigma_{l=2}^{n} c_l(l-1) , m=1\ 0, m=n\ min{OPT(n-1,m-1),OPT(n-1,m) + c_n(n-l_m)} end{cases}$$

My thought was that if I solve the problem top-bottom than on any floor n I have 2 option or not transform the boxes to another floor or transform the boxes to $$l_m$$ of the subproblem.
but that optimal structure doesn’t work because in the example

$$OPT(4,2)= 7 ,OPT(4,1) = 23$$ and according to that optimal structure $$OPT(5,2) = 23$$ but there is soultuon with cost of 16.

## dynamic programing. minimum path difference between coloring edges

consider the next problom:

input: directed Graph $$G(V,E)$$ and $$c:E->{blue,red}$$ and vertiecs $$s$$ s.t there is a path from $$s$$
for all the vertices in $$V$$ . G may contain cycles.

**Define:the difference of path $$P$$ is $$Delta P = | |{ein P|c(e) = blue} | – |{ein P|c(e) = red}| |$$

i.e the difference between the number of red edges and blue edges.

output: find $$forall v in V$$ the minimum $$Delta P$$

I need to find a dynamic programming solution to the problem.

I want to find an optimal structure for the problem by:
$$opt(v) = min_{u in In(v)}(opt(u) + f(u,v))$$

$$f(u,v)$$ define:

$$f(u,v) = begin{cases} 1, & text{the color of (u,v) same as the color that appears more}, \ -1, & text{the color of (u,v) the color different from the color that appears more}.\ end{cases}$$

the problem with that formula is that the optimum of the neighbors, not gratitude the optimum solution for $$v$$ because considering $$v$$ that have 1 neighbor with a blue edge that has a path with the same blue and red edges and another path with 1 more red edge than my structure return 1 however there is a path with a difference of 0.

## Fixed partitioning and dynamic partitioning process allocation

If I have a process leave of 10KB and only that 10KB is free then 2 processes of 2KB and 6KB enter into the memory will only one process get allocated or both in the same hole.

## unity – Check if NavMeshAgent can reach position with dynamic obstacles

Thanks for contributing an answer to Game Development Stack Exchange!

But avoid

• Making statements based on opinion; back them up with references or personal experience.

Use MathJax to format equations. MathJax reference.

## dynamic – Function not evaluated in CloudDeplot

I am trying to create a LocatorPane on the Wolfram cloud, but am hitting a snag. The LocatorPane needs a couple of helper functions. When run locally, everything works fine. When I deploy it to the cloud, these helper functions are not evaluated.

Here is a toy example that shows the issue

CloudDeploy[
dog[pt_]:=Norm[pt];
DynamicModule[{pt={1,1}/2},
{LocatorPane[Dynamic[pt],Dynamic@If[Norm[pt]<1.,Graphics[{Gray,Disk[]}],Graphics[{Red,Disk[]}]]],Dynamic[pt],Dynamic[dog[pt]]}]
,Permissions->"Public"]


In addition to displaying a disk, this should also display the coordinates of the locator and evaluate the function dog. Instead, the dog function is not evaluated. Any suggestions would be appreciated.

## c++ – Code A Primitive Calculator ( x3,x2,+1) Using Dynamic programming

Question -You are given a primitive calculator that can perform the following three operations with the current number 𝑥: multiply 𝑥 by 2, multiply 𝑥 by 3, or add 1 to 𝑥. Your goal is given apositive integer 𝑛, find the minimum number of operations needed to obtain the number 𝑛
starting from the number 1.

ll n;
cin>>n;

vector<ll>vec;
vec.pb(n);

if(n==1)
{
cout<<0<<endl<<1;
}

else
{
while(n!=1)
{
if(n%3==0)
n=n/3;
else if(n%2==0)
n=n/2;
else
n=n-1;

vec.pb(n);
}

cout<<vec.size()-1<<endl;

for(int i=(vec.size()-1);i>=0;i--)
cout<<vec(i)<<" ";
}
return 0;


}

for input 96234 iam getting

15

1 2 4 5 10 11 22 66 198 594 1782 5346 16038 16039 32078 96234

but the optimal soln is

14

1 3 9 10 11 22 66 198 594 1782 5346 16038 16039 32078 96234

i know i am going wrong at the step when 10 is converted into 5 in my code but it should convert it in to 9, Please help me,

## forms – How to check if dynamic select query returns zero records and return markup

I have a custom Drupal form that is populated with a dynamic query. How do I return an ‘alert’ if no results are returned? There has to be a better way of doing this than what I have come up with.

This is what I have so far and it throws an error when a record is returned because it continues processing the actual form.

Query:

public function loadByPID($pid = null) {$database = DrupalCoreDatabaseDatabase::getConnection('default', 'newDB');
$query =$database->select ('Person');
$query->condition('personid',$pid);
$query->fields('Person', ('col1', 'col2', 'col3'));$results = $query->execute(); return$results;
}


Call to query:

$results =$this->repository->loadByPID($pid);  The form and my attempt to check if there are zero records returned. $rowCount = 0;
foreach ($results as$record) {
$rowCount++; } if ($rowCount == 0) {
$form('no_values') = ( '#markup' =>$this->t('No entries exist.'),
'#suffix' => '</div>',
);
return $form; }else{ // the actual form$form()...
return \$form;
};


## Creating a dynamic copy of another Google Sheet, in which new columns could always be linked to the dynamic copy

In a dynamic copy of an existing spreadsheet (that is also updated with new lines), I made a dynamic copy in another spreadsheet, in which I added columns. All good here.

I use the new columns to complete the information from the dynamic copy, but when a new line is added in the original document, it takes the information I had for the previous line :

Example :

A2 is the new column is the final document
B2 to D2 is the dynamic copy I inserted in that final document
I fill that A2 with information I need to complete B2 to D2
A line is the added in the original document and becomes A2 now
A2 in the final document is the added line, and all the information that I added for the previous A2 complete the wrong line.

Do you know how I can solve this ?

## ssh – Fileserver with dynamic files

This may sound a bit odd, but I am trying to make it so that the files that get served can be chosen dynamically. In details, I want to perform 2 operations.

1. For all .txt files, replace {USER} with the current username.
2. Some files will appear in multiple directories, but I don’t want them to take up additional storage. So if any of those files is requested, I want to server the one real file that I have.

Now, I could just implement the SFTP protocol in some language like java, and do this myself. However, I don’t really feel like re-implementing the SFTP protocol. After all, I need is the normal protocol, but I change the serve function a bit. Is there an easy way to deal with this?

Note that it doesn’t need to be SFTP, as long as it’s safe and it supports authentication, I am happy.