Postgresql: sort by value position in array column, then by secondary order

I’m not quite sure what the best way to phrase this is…

So in my DB there is pillars text array which is basically an enum where providers ordered what values meant the most to their business, from most important to providing that value for their clients, to least important.

I’m using PostGIS to query providers in a specific area, and want to return providers ordered first by the pillar that a client selected they were looking for, then by closest location.

so if the pillars all have values ['a', 'b', 'c', 'd'], in any order depending on what providers selected, and the client selected pillar c

the results of the query would preferably return any/all providers that have pillar c at array index 0 first, ordered by distance to geopoint, then by providers that have pillar c at array index 1 second ordered by distance to client geopoint, then idx 2, then idx 3

I’m really only looking for the top 3 results in all cases, and providers with pillar c at idx 1 would only be needed if there were less than 3 results for index 0

Is this possible to pull off in a single query? or should I just run it with a where clause and check the results length until I have 3 results?

The pillars column is indexed with a gin index btw

WordPress meta query for serialize associated array meta value

Serialize array value is:

a:13:{s:7:"relaxed";a:7:{s:4:"name";s:7:"Relaxed";s:5:"votes";i:377;s:7:"term_id";s:5:"28595";s:4:"slug";s:7:"relaxed";s:4:"rank";i:1;s:6:"status";b:1;s:4:"icon";s:70:"https://example.com/wp-content/uploads/2020/12/Relaxed.svg";}s:5:"happy";a:7:{s:4:"name";s:5:"Happy";s:5:"votes";i:293;s:7:"term_id";s:5:"28596";s:4:"slug";s:5:"happy";s:4:"rank";i:2;s:6:"status";b:1;s:4:"icon";s:68:"https://example.com/wp-content/uploads/2020/12/Happy.svg";}s:8:"euphoric";a:7:{s:4:"name";s:8:"Euphoric";s:5:"votes";i:267;s:7:"term_id";s:5:"28597";s:4:"slug";s:8:"euphoric";s:4:"rank";i:3;s:6:"status";b:1;s:4:"icon";s:71:"https://example.com/wp-content/uploads/2020/12/Euphoric.svg";}s:8:"uplifted";a:7:{s:4:"name";s:8:"Uplifted";s:5:"votes";i:191;s:7:"term_id";s:5:"28598";s:4:"slug";s:8:"uplifted";s:4:"rank";i:4;s:6:"status";b:1;s:4:"icon";s:71:"https://example.com/wp-content/uploads/2020/12/Uplifted.svg";}s:6:"sleepy";a:7:{s:4:"name";s:6:"Sleepy";s:5:"votes";i:181;s:7:"term_id";s:5:"28599";s:4:"slug";s:6:"sleepy";s:4:"rank";i:5;s:6:"status";b:1;s:4:"icon";s:69:"https://example.com/wp-content/uploads/2020/12/Sleepy.svg";}s:6:"hungry";a:7:{s:4:"name";s:6:"Hungry";s:5:"votes";i:178;s:7:"term_id";s:5:"28600";s:4:"slug";s:6:"hungry";s:4:"rank";i:6;s:6:"status";b:1;s:4:"icon";s:69:"https://example.com/wp-content/uploads/2021/01/Hungry.svg";}s:6:"giggly";a:7:{s:4:"name";s:6:"Giggly";s:5:"votes";i:122;s:7:"term_id";s:5:"28602";s:4:"slug";s:6:"giggly";s:4:"rank";i:7;s:6:"status";b:1;s:4:"icon";s:69:"https://example.com/wp-content/uploads/2021/01/Giggly.svg";}s:8:"creative";a:7:{s:4:"name";s:8:"Creative";s:5:"votes";i:109;s:7:"term_id";s:5:"28601";s:4:"slug";s:8:"creative";s:4:"rank";i:8;s:6:"status";b:1;s:4:"icon";s:71:"https://example.com/wp-content/uploads/2021/01/Creative.svg";}s:6:"tingly";a:7:{s:4:"name";s:6:"Tingly";s:5:"votes";i:107;s:7:"term_id";s:5:"28605";s:4:"slug";s:6:"tingly";s:4:"rank";i:9;s:6:"status";b:1;s:4:"icon";s:69:"https://example.com/wp-content/uploads/2021/01/Tingly.svg";}s:7:"focused";a:7:{s:4:"name";s:7:"Focused";s:5:"votes";i:102;s:7:"term_id";s:5:"28603";s:4:"slug";s:7:"focused";s:4:"rank";i:10;s:6:"status";b:1;s:4:"icon";s:70:"https://example.com/wp-content/uploads/2021/01/focused.svg";}s:9:"talkative";a:7:{s:4:"name";s:9:"Talkative";s:5:"votes";i:99;s:7:"term_id";s:5:"28606";s:4:"slug";s:9:"talkative";s:4:"rank";i:11;s:6:"status";b:1;s:4:"icon";s:72:"https://example.com/wp-content/uploads/2021/01/Talkative.svg";}s:7:"aroused";a:7:{s:4:"name";s:7:"Aroused";s:5:"votes";i:74;s:7:"term_id";s:5:"28607";s:4:"slug";s:7:"aroused";s:4:"rank";i:12;s:6:"status";b:1;s:4:"icon";s:70:"https://example.com/wp-content/uploads/2021/01/Aroused.svg";}s:9:"energetic";a:7:{s:4:"name";s:9:"Energetic";s:5:"votes";i:65;s:7:"term_id";s:5:"28604";s:4:"slug";s:9:"energetic";s:4:"rank";i:13;s:6:"status";b:1;s:4:"icon";s:72:"https://example.com/wp-content/uploads/2021/01/Energetic.svg";}} 

And data in array format:

Array (     [relaxed] => Array         (             [name] => Relaxed             [votes] => 377             [term_id] => 28595             [slug] => relaxed             [rank] => 1             [status] => 1             [icon] => https://example.com/wp-content/uploads/2020/12/Relaxed.svg         )      [happy] => Array         (             [name] => Happy             [votes] => 293             [term_id] => 28596             [slug] => happy             [rank] => 2             [status] => 1             [icon] => https://example.com/wp-content/uploads/2020/12/Happy.svg         )      [euphoric] => Array         (             [name] => Euphoric             [votes] => 267             [term_id] => 28597             [slug] => euphoric             [rank] => 3             [status] => 1             [icon] => https://example.com/wp-content/uploads/2020/12/Euphoric.svg         )      [uplifted] => Array         (             [name] => Uplifted             [votes] => 191             [term_id] => 28598             [slug] => uplifted             [rank] => 4             [status] => 1             [icon] => https://example.com/wp-content/uploads/2020/12/Uplifted.svg         )      [sleepy] => Array         (             [name] => Sleepy             [votes] => 181             [term_id] => 28599             [slug] => sleepy             [rank] => 5             [status] => 1             [icon] => https://example.com/wp-content/uploads/2020/12/Sleepy.svg         )      [hungry] => Array         (             [name] => Hungry             [votes] => 178             [term_id] => 28600             [slug] => hungry             [rank] => 6             [status] => 1             [icon] => https://example.com/wp-content/uploads/2021/01/Hungry.svg         )      [giggly] => Array         (             [name] => Giggly             [votes] => 122             [term_id] => 28602             [slug] => giggly             [rank] => 7             [status] => 1             [icon] => https://example.com/wp-content/uploads/2021/01/Giggly.svg         )      [creative] => Array         (             [name] => Creative             [votes] => 109             [term_id] => 28601             [slug] => creative             [rank] => 8             [status] => 1             [icon] => https://example.com/wp-content/uploads/2021/01/Creative.svg         )      [tingly] => Array         (             [name] => Tingly             [votes] => 107             [term_id] => 28605             [slug] => tingly             [rank] => 9             [status] => 1             [icon] => https://example.com/wp-content/uploads/2021/01/Tingly.svg         )      [focused] => Array         (             [name] => Focused             [votes] => 102             [term_id] => 28603             [slug] => focused             [rank] => 10             [status] => 1             [icon] => https://example.com/wp-content/uploads/2021/01/focused.svg         )      [talkative] => Array         (             [name] => Talkative             [votes] => 99             [term_id] => 28606             [slug] => talkative             [rank] => 11             [status] => 1             [icon] => https://example.com/wp-content/uploads/2021/01/Talkative.svg         )      [aroused] => Array         (             [name] => Aroused             [votes] => 74             [term_id] => 28607             [slug] => aroused             [rank] => 12             [status] => 1             [icon] => https://example.com/wp-content/uploads/2021/01/Aroused.svg         )      [energetic] => Array         (             [name] => Energetic             [votes] => 65             [term_id] => 28604             [slug] => energetic             [rank] => 13             [status] => 1             [icon] => https://example.com/wp-content/uploads/2021/01/Energetic.svg         )  ) 

I want to match the slug and get rank between 1 to 5 only. Let me know if there any solution for this.

Do Continuous effects in an array remain after you switch to a different power?

Note: This is different to What happens when you use a continuous power in an array and then switch to a different power? as that covers 2nd edition, and I’m focused on 3rd.

Say you have an array with two powers: one is a Create effect with Increased Duration (Continuous) extra, the other is an attack of some kind. You use the former to create a box, and then switch to the other. Does the box remain, or does it vanish?

The rules are ambiguous on this point, and there is a strong argument for either position from both a rules and a thematic perspective

The description for Alternate Effect states:

Alternate Effects cannot be used or maintained at the same time as other Alternates in the same array; they are mutually exclusive

The description for the Continuous duration states:

The effect lasts as long as you wish, without any action required on your part. Once active, it stays that way until you choose to deactivate it

From a rules perspective, one could argue that since one is not maintaining the effect, one would be permitted to swap between powers without dismissing the effect. On the other hand, one could argue that the effect being in place at all counts as the power being ‘used’.

From a thematic perspective, one could argue for effects remaining with the following example: Imagine a superhero, let’s call her Sue. Sue has the power to fire Ice Beams, with two applications: as an attack, or to build ice constructs. Since both rely on her firing Ice Beams, and she can only use the beams for one at a time, it makes sense that they would be an array. In addition, once her constructs are created, she doesn’t need to sustain them (ignoring ice melting for this example). As such, it would make sense for the constructs to be Continuous. If she were to build a construct one round, then attack with her beams the next, it would make little sense for the construct to suddenly disappear, as they were not reliant on her in any way. However, one could argue that the two effects being able to be applicable at the same time violates the concept of an array.

I’ve checked the forums, and there is a solid 3 pages of discussion there with no consensus, so I’m looking for an official ruling on this if there is one at all. Thanks in advance.

How to get a value-only flat array from $wpdb->get_results when selecting a single column, without foreach()?

My query is:

$  var = $  wpdb->get_results("SELECT field FROM {$  wpdb->prefix}table", ARRAY_A); var_dump($  var); 

it returns someting like array(2) { [0]=> array(1) { ["field"]=> string(5) "test1" } [1]=> array(1) { ["field"]=> string(t) "test2" }. I.e. each item is a row with a single name-value pair.

What I want is array(2) { [0]=> string(5) "test1" [1]=> string(5) "test2" }

Currently I achieve it like this:

$  var = $  wpdb->get_results("SELECT field FROM {$  wpdb->prefix}table"); foreach($  var as $  v_key => $  v_val) $  var[$  v_key] = $  v_val['field']; var_dump($  var); 

Is there a shorter way to do this?

3D plot from an array

I have an array on the following form:

Array[1, {20, 20, 3}]; 

and i want to visualise the data in 3D plot, i tried many times but usually Mathematica reply that there is an error.

here is a part of the code enter image description here

part of the data

{{{0, 0, {0.20667}}, {0, 1, {0.584291}}, {0, 2, {0.948858}}, {0,3, {0.922532}}}, {{1, 0, {0.20667}}, {1, 1, {0.188105}}, {1,2, {0.000314782}}, {1, 3, {0.163525}}}, {{2, 0, {0.20667}}, {2,1, {0.948938}}, {2, 2, {0.0784778}}, {2, 3, {0.00361825}}}, {{3,0, {0.20667}}, {3, 1, {0.772576}}, {3, 2, {0.466603}}, {3,3, {0.00774734}}}} 

How to make a discrete circle in a 2d array?

I’m trying to make a filled-in circle in a 2d matrix. Any value less than r is 1, and any value outside of r is zero. I’m looking for something that looks more circular as the array size grows. The circle should be centered in the array

I’m starting out with an array full of zeros, and trying to use a loop to assign values of 1, but I’m not getting anything that looks remotely like a circle.

Here’s what I’ve tried:

width = 100 height = 100 radius = 20 halfwidth = width/2 halfheight=height/2  array = ConstantArray[0, {height, width}] For[i=0, i<width, i++; For j=0, j<height, j++; If[i*i +j*j < radius*radius, array[[i + halfheight -  radius, j+halfwidth-radius]]=1]]]] 

The last line SHOULD iterate over the entire 2d array and then checks if that index falls within the circle. If True, it assigns a value of 1, else it does nothing. Obviously, it’s not doing that. This is what it produces:

Not very circular circle

It looks like it might be creating a single quadrant of the circle, but if so its in the wrong place.

So, how do I center this, and make a complete circle?

Thanks!

WordPress primitive query returns = array

i am trying to primitive query form this wp_query:

$  query_v = array(     'post_type' => array('pro'),     'posts_per_page'    => -1,     'orderby' => $  count_visit,     'order' => 'ASC',     'tax_query' => array(         array(             'taxonomy' => 'servizi_pro',             'field' => 'slug',             'terms' => $  art_p,             'parent' => 0         )     ) ); 

the problem with this wp_query is that it does not sort me by the number of post views counter that I downloaded as an additional plugin, and I am trying to implement in a primita wordpress query, the wp_query with the addition of the instructions to display the type of custom posts based on the number of views:

<?php global $  wpdb; $  cpt = "pro";  $  q = $  wpdb->get_results(     $  wpdb->prepare(" SELECT * FROM {$  wpdb->prefix}term_relationships  LEFT JOIN {$  wpdb->prefix}terms ON {$  wpdb->prefix}term_relationships.term_taxonomy_id = {$  wpdb->prefix}terms.term_id  LEFT JOIN term_taxonomy ON {$  wpdb->prefix}term_relationships.term_taxonomy_id = {$  wpdb->prefix}term_taxonomy.term_id  LEFT JOIN {$  wpdb->prefix}posts  ON {$  wpdb->prefix}term_relationships.object_id = {$  wpdb->prefix}posts.ID  LEFT JOIN post_views ON {$  wpdb->prefix}term_relationships.object_id = {$  wpdb->prefix}post_views.id  WHERE  {$  wpdb->prefix}post_views.type = '4'    AND {$  wpdb->prefix}posts.post_type = '$  cpt'  AND  {$  wpdb->prefix}terms.name = '$  art_p'         ") ); ?>  <?php print_r($  q);?> <?php foreach ($  q as $  r){ ?>     <?php echo "{$  r->post_title}"; ?> <?php } ?> 

but it always returns to me array (), where am I wrong?

How to prevent SQL Injection via the array parameter? (CVE-2017-14069)

Hello, this page suggest that the sql_query

$  r = sql_query("SELECT modcomment FROM users WHERE id IN (" . implode(", ", $  _POST[usernw]) . ")")or sqlerr(__FILE__, __LINE__);
Code (SQL):

is vulnerable to a SQL injection "via the usernw array parameter to nowarn.php."

and the exploit is suggested:

POST nowarned=nowarned&usernw[]=(select*from(select sleep(10))x)
Code (markup):

Please how that sql_query should…

How to prevent SQL Injection via the array parameter? (CVE-2017-14069)

How can I properly loop through an array gotten from my wordpress database and display it to my site

I am trying to display some posts information from my database on my live site. I have done the query correctly because I can view the items on the live site. But I need to take three items to display. The post title, the post content which houses the images, and the post id which links to the posts. I seem to have written it correctly but it does not do anything.

This is the code

<?php      global $  wpdb;                    $  results = $  wpdb->get_results( "SELECT * FROM $  wpdb->posts WHERE `post_type`='post' AND `post_status` = 'publish' LIMIT    20" );    foreach ($  results) {       echo "<div class="card">             <div class="img">              <img src="{post_content}" alt="">             </div>             <div class="info">                <h5>{post_title}</h5>             </div>           </div>";   }    //echo "<pre>";print_r($  results);echo"</pre>";                      ?>  

I am quite new to wordpress and PHP in general.

Time complexity of pairs in array double loop

I know, that the following is: O(n^2),

int count = 0; for(int i = 0; i<array.length(); i++) {    for(int j = i+1; j<array.length(); j++) {        if(array[i] == array[j]) {            count = count + 1;        }    } } 

But, should something like count = count + 1; be taken into account? For predicting or making up a complex time equation, or using sum notation,

n + n-1 + n-2 + n-3 + (…) + 1