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.
Please answer with respect to fixed partitioning and dynamic partitioning.

unity – Check if NavMeshAgent can reach position with dynamic obstacles

Thanks for contributing an answer to Game Development Stack Exchange!

  • Please be sure to answer the question. Provide details and share your research!

But avoid

  • Asking for help, clarification, or responding to other answers.
  • Making statements based on opinion; back them up with references or personal experience.

Use MathJax to format equations. MathJax reference.

To learn more, see our tips on writing great answers.

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.

Output of code

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) {
  // alert if no values
  $form('no_values') = (
    '#markup' => $this->t('No entries exist.'),
    '#prefix' => '<div class="alert alert-warning my-5" role="alert">',
    '#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.