Tag: Query

wp query – How to search by a post and a category name on wordpress at the same time?

I wanna change the default behavior of wordpress search engine. Basically, I want to choose between 2 paths when the user informs a term on the search input:

  • Find the posts or custom posts that have that informed term
  • Or find the posts that belong to a taxonomy (or a custom taxonomy) that
    has a name like the informed term

So far, I’m trying to use the hook pre_get_posts but I don’t know how to combine the 2 paths. I did try something like this:

function change_posts_per_page( $query ) {
    $query->set(
        'tax_query', array(
            array(
                'taxonomy' => 'course-category',
                'field'    => 'name',
                'terms'    => get_query_var('s')
            )
    )); 
}

add_action( 'pre_get_posts', 'change_posts_per_page' );

It doesn’t bring the posts with a taxonomy that has the name get_query_var(‘s’). Can someone help me?

mysql – Executing a query on the production to fix erroring data

During the creation of some data on the production server by our backend, an unexpected error occurred and some data became inaccessible. Currently, a fix is being found for the error, but the flawed data is still on the server, causing some trouble. This can easily be fixed by setting some data to NULL. The question is, how should we handle this? Our backend uses the Spring framework and Liquibase to update the schema when necessary. Our production server is managed by a different company and is unaccessible for us (the developers). The other company wants us to write an entry for the Liquibase XML file to fix this. We are questioning this decision, but running a query on the production database seems tricky too.

Note: we are running more than just the production environment, so by adding the query to the Liquibase XML file, it will run on all environments.

sql server – How do I parameterize an SQL query using Excel’s “Get Data” function

I am attempting to import data from an SQL Server instance hosted on Azure into Excel (Excel 365 desktop app)

I can get the basic query to work by going to Data>Get Data>From Database>From SQL Server Database

Lets suppose the initial query (entered into the SQL statement box under the advanced options in the dialog box produced by the command above) is EXECUTE dbo.getClient 2. This works and returns data correctly

What I want to do is parameterize this query to EXECUTE dbo.getClient (Sheet1$b1) in a similar way to that suggested here Excel: Use a cell value as a parameter for a SQL query However attempting to do this gives the error “Microsoft SQL: Error converting data type nvarchar to int.”

I then attempted to cast thus EXECUTE dbo.getClient SELECT CAST((Sheet1$b1:b1) AS int) but this gives the error “Microsoft SQL: Invalid column name ‘Sheet1$b1:b1’.”

So how do I parameterize Excel queries to an SQL server database? Answers which avoid macro scripts would be preferred if possible. I would like to be able to update the parameter cells in the workbook and then just go to Data>Refresh All.

postgresql – Postgres not returning data on array_agg query as below

The problem arise when there are no data for books in specific library. Consider a following working scenario.

Table name: library

--------------------------------
| id |    name     |    owner  |
--------------------------------
|  1 |     ABC     |     A     |
|  2 |     DEF     |     D     |
|  3 |     GHI     |     G     |
--------------------------------

Table name: books

--------------------------------
| id |    title    |  library  |
--------------------------------
|  a |     xxx     |     1     |
|  b |     yyy     |     1     |
|  c |     zzz     |     2     |
--------------------------------

Now when I do query like below:

SELECT library.name, array_agg(b.title) AS book_list FROM library, 
(SELECT title FROM books WHERE books.library = :library_no) as b 
WHERE library.id = :library_no GROUP BY library.id

The query generates output for library 1 & 2, but not for library 3. Why and how to solve this issue? (Generate an empty list on no library books)

Required Output:

----------------------
| name |    book_list |
----------------------
|  GHI |      {}      |   # or {null}
-----------------------

PS: I’ve even tried coalesce as below:

SELECT library.name, coalesce(array_agg(b.title), ARRAY()::VARCHAR()) AS book_list FROM library, 
(SELECT title FROM books WHERE books.library = :library_no) as b 
WHERE library.id = :library_no GROUP BY library.id

Postgres version: 12

postgresql – Postgres not returning data on array_agg query as below

The problem arise when there are no data for books in specific library. Consider a following working scenario.

Table name: library

--------------------------------
| id |    name     |    owner  |
--------------------------------
|  1 |     ABC     |     A     |
|  2 |     DEF     |     D     |
|  3 |     GHI     |     G     |
--------------------------------

Table name: books

--------------------------------
| id |    title    |  library  |
--------------------------------
|  a |     ABC     |     1     |
|  b |     DEF     |     1     |
|  c |     GHI     |     2     |
--------------------------------

Now when I do query like below:

SELECT library.name, array_agg(b.title) AS book_list FROM library, 
(SELECT title FROM books WHERE books.library = :library_no) as b 
WHERE library.id = :library_no GROUP BY library.id)

The query generates output for library 1, but not for library 3. Why and how to solve this issue? (Generate an empty list on no library books)

sharepoint rest api – Query string prohibited depending on the statement

The problem

I have a List with loads of rows and I’m doing a workaround in Python to fetch them without exceding the list view threshold. So far I managed to do this by limiting the ID range of the results, like so:

<...>/_api/Web/lists/GetByTitle('<ListName>')/Items?$select=ID&$top=5000&$orderby=ID&$filter=ID ge 1 and ID le 5000

And then I change the numbers of ‘ID’ column from 1~5000 to 5001~10000, and so on…

Some Queries

So this query here works fine:

<...>/_api/Web/lists/GetByTitle('<ListName>')/Items?$select=*&$top=5000&$orderby=ID&$filter=ID ge 20001 and ID le 25000 and TextField eq 'Some text'

And I manage to get 5000 rows from each request made like this (even though there are more than 5000 rows with ‘Some text’ inside the ‘TextField’ column).

But if I try this one here:

<...>/_api/Web/lists/GetByTitle('<ListName>')/Items?$select=*&$top=5000&$orderby=ID&$filter=ID ge 20001 and ID le 25000 and DateField gt '2020-09-12T00:00:00'

It raises me the limit exception. And if I change the date to a day that I know it’ll rerturn less then 5000 rows, it runs just fine.

In Sum

I’m limiting the range with the ID column of SharePoint list so it doesn’t exceed the view threshold, but it doesn’t seem consistent.

This is the template i’m using, adding things at the end, and only changing the range until the list is over:

<...>$filter=ID ge 1 and ID le 5000 and <other query options...>

Any ideas on why SharePoint thinks I’m trying to fetch more than 5000 rows at once? Thanks in advance!

query performance – MySQL compare multiple values in WHERE clause, find subset

I have a table segments with column comp_id, segment_id.

comp_id: 1
segment_id: 23,11,223,3331;
comp_id: 2
segment_id: 231,11,223,3331;

I have another table: comps with segment_id as well.

comp_id: 1
segment_id: 11, 3331
comp_id: 3
segment_id: 7, 63

I need to filter out rows from comp where all segment_id values are inside segment_id from segments table.

This is a simplified version of what I need to do. simple JOIN statements wouldn’t help, I need to do this inside WHERE clause.

i.e.

WHERE
comp.segment_id = ALL (SELECT segment_id FROM segments)

this won’t work because this compares single “comp.segment_id”, I need all “comp.segment_id”s compared.

Is there any function or some method of doing this? And btw performance is crusial.
Is there any intersect methods to do so?
To simplify I need to find subsets of data.
Actual query is very big and I won’t post it here, don’t give me JOIN examples, please.

linear algebra – Appending to list from For loop query

I am new to Mathematica, hence the following basic task I am trying to code is as follows:

Given matrix $M = begin{pmatrix}
2 & -1 \
1 & 2
end{pmatrix}$, sum the overlap (inner-product) of it’s eigenvectors $bigg{begin{pmatrix}i \1 end{pmatrix} ,begin{pmatrix}-i \1 end{pmatrix} bigg}$. To this end I first reshape to form arrays with correct dimensions. I am then able to generate and print the overlap values in the form of a 1×1 array for each overlap pair, but am then having difficulty appending the result to a list (as can bee seen by the empty list L2 in the output), which I then plan to sum.
Can anyone advise on what the problem is with the attached code? Thanks for any assistance.

Appending Matrices to Lists Query

In(2197):= M = ( {
    {2, -1},
    {1, 2}
   } );

In(2198):= Eig = Eigenvectors(M)
Eval = Eigenvalues(M) ;

L1 = {};
L2 = {};

For(i = 1, i <= Length(M), i++,  
 AppendTo(L1, ArrayReshape(Eig((i)), {Length(M), 1})))

For(i = 1, i <= Length(M), i++, 
 For(j = 1, j <= Length(M), j++ , 
  Print(Dot(ConjugateTranspose(L1((i))), L1((j)))  ))) 

For(i = 1, i <= Length(Gd2), i++, 
 For(j = 1, j <= Length(Gd2), j++ , 
  Append(L2 , Dot(ConjugateTranspose(L1((i))), L1((j)))  ))) 

Print(L1)
Print(L2)



Out(2198)= {{I, 1}, {-I, 1}}

During evaluation of In(2198):= {{2}}

During evaluation of In(2198):= {{0}}

During evaluation of In(2198):= {{0}}

During evaluation of In(2198):= {{2}}

During evaluation of In(2198):= {{{I},{1}},{{-I},{1}}}

During evaluation of In(2198):= {}
```

profiler – what is the statistics part of a query profile?

I’m using Neor Profile SQL to check some bottlenecks in my DB execution queries and trying to profile a single one to understand how to improve it.

The query

SELECT COUNT(DISTINCT e.entity_id)
FROM `catalog_product_entity` AS `e`
         INNER JOIN `catalog_product_index_price` AS `price_index`
                    ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND
                       price_index.customer_group_id = 0
         INNER JOIN `catalog_category_product_index` AS `cat_index`
                    ON cat_index.product_id = e.entity_id AND cat_index.store_id = '1' AND
                       cat_index.visibility IN (3, 4) AND cat_index.category_id = '2'
WHERE (`e`.`entity_id` IN
       (78661, 78664, 78668, 78659, 78658, 78657, 78654, 78655, 78653, 78650, 78651, 78649, 78647, 78645, 78644, 78643,
        78642, 78641, 78638, 78640, 78637, 78635, 78633, 78627, 78626, 78619, 78603, 78600, 78599, 78598, 78596, 78594,
        78590, 78589, 78588, 78587, 78586, 78585, 78582, 78581, 78580, 78579, 78577, 78576, 78575, 78574, 78565, 78566,
        78562, 78551, 78550, 78546, 78542, 78538, 78535, 78532, 78530, 78529, 78527, 78526, 78524, 78518, 78508, 78510,
        78507, 78506, 78505, 78504, 78503, 78502, 78501, 78499, 78500, 78498, 78496, 78495, 78494, 78493, 78492, 78490,
        78491, 78489, 78480, 78485, 78477, 78475, 78473, 78470, 78468, 78460, 78465, 78456, 78455, 78448, 78441, 78445,
        78439, 78438, 78421, 78422, 78420, 78419, 78418, 78415, 78417, 78414, 78413, 78412, 78411, 78410, 78409, 78408,
        78405, 78404, 78403, 78398, 78392, 78391, 78390, 78386, 78376, 78374, 78361, 78360, 78355, 78352, 78347, 78345,
        78346, 78343, 78339, 78342, 78335, 78329, 78328, 78319, 78317, 78310, 78308, 78302, 78297, 78294, 78293, 78292,
        78284, 78278, 78275, 78274, 78272, 78271, 78269, 78267, 78265, 78263, 78254, 78253, 78251, 78250, 78249, 78247,
        78240, 78239, 78231, 78228, 78222, 78220, ... quite a few more ids like the above));

The explain

+--+-----------+-----------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+-------+----------------------------------------------+----+------------------------+
|id|select_type|table      |type  |possible_keys                                                                                                                                           |key                             |key_len|ref                                           |rows|Extra                   |
+--+-----------+-----------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+-------+----------------------------------------------+----+------------------------+
|1 |SIMPLE     |cat_index  |ref   |PRIMARY,IDX_CAT_CTGR_PRD_IDX_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY,15D3C269665C74C2219037D534F4B0DC                                                        |15D3C269665C74C2219037D534F4B0DC|6      |const,const                                   |25  |Using where; Using index|
|1 |SIMPLE     |e          |eq_ref|PRIMARY                                                                                                                                                 |PRIMARY                         |4      |db_easyoffers.cat_index.product_id            |1   |Using index             |
|1 |SIMPLE     |price_index|eq_ref|PRIMARY,IDX_CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID,IDX_CATALOG_PRODUCT_INDEX_PRICE_WEBSITE_ID,IDX_CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE|PRIMARY                         |8      |db_easyoffers.cat_index.product_id,const,const|1   |Using index             |
+--+-----------+-----------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+-------+----------------------------------------------+----+------------------------+

The profile

| Status               | Duration  |
| -------------------- | --------- |
| starting             | 0,001545  |
| checking permissions | 0,000005  |
| checking permissions | 0,000002  |
| checking permissions | 0,000004  |
| Opening tables       | 0,000027  |
| After opening tables | 0,000005  |
| System lock          | 0,000004  |
| Table lock           | 0,000005  |
| init                 | 0,000784  |
| optimizing           | 0,00054   |
| statistics           | 13,078536 |
| preparing            | 0,000251  |
| executing            | 0,000003  |
| Sending data         | 0,009422  |
| end                  | 0,000056  |
| removing tmp table   | 0,000004  |
| end                  | 0,000003  |
| query end            | 0,000006  |
| closing tables       | 0,000002  |
| Unlocking tables     | 0,000006  |
| freeing items        | 0,000033  |
| updating status      | 0,00004   |
| cleaning up          | 0,000085  |

So what exactly is the row statistics and how can I see why it’s taking so long so I can troubleshoot further?

8 – Database query a table where field type is MEDIUMTEXT

I’m trying to get webform submission data from the database. My webform has a hidden field for a related node id, but I cannot use a query with a condition on the field. In the database, the field type for value is MEDIUMTEXT. If the value is a string, I can call the condition as a quoted string, but since it’s a number, I get no results. Whether I pass the value as quoted or not, I get no results.

How can I pass in the proper value to get any results?

$query = $database->select('webform_submission_data', 'wsd')
      ->condition('name', 'node_id', '=')
      ->condition('webform_id', 'scores', '=')
      //this is mediumtext in the database
      ->condition('value', "169", '=')
      ->fields('wsd', ('sid'));
    $result = $query->execute();