CAML query not able to filter data retrieved from SharePoint

I have been working on a SharePoint Online System which stores data related to various products in different document libraries and lists.
In this SharePoint Online System there is a requirement of implementing an API which will allow users to download folders into a zip from the libraries present in the site.
The API code works well with the libraries which have items less than 5000, but it gives us an error of “list threshold” when the number of item is higher than 5000.

On further investigation I was able to get past the error by traversing the whole list with the help of a CAML query then use LINKQ to filter out item.
But as there are over 80,000 items in the list it takes a long time. To resolve this I made changes in the CAML Query so that I can get only the items that are required from SharePoint rather than applying the LINKQ.
Even after adding condition in CAML system simply ignores the condition and returns all the list items as it was doing before.

This is the code I am working with,

class Program
{
static void Main(string() args)
{
List<ListItem> items = new List<ListItem>();
string subFolders = "Test-Test_123";
using (ClientContext ctx = new ClientContext("https://domain.sharepoint.com/"))
{
string password = "********";
string account = "user@domain.onmicrosoft.com";
var secret = new SecureString();
foreach (char c in password)
{
secret.AppendChar(c);
}
ctx.Credentials = new SharePointOnlineCredentials(account, secret);

Web web = ctx.Web;
ctx.Load(web, wb => wb.ServerRelativeUrl);
ctx.ExecuteQuery();

List list = web.Lists.GetByTitle("product");
ctx.Load(list);
ctx.ExecuteQuery();

CamlQuery camlQuery = new CamlQuery();

camlQuery.ViewXml = "<View Scope='Recursive'><Where><BeginsWith><FieldRef Name='Test'/></BeginsWith></Where><RowLimit>5000</RowLimit></View>";
//Filter path or Name begins with Test
do
{
ListItemCollection listItems = list.GetItems(camlQuery);
ctx.Load(listItems);
ctx.ExecuteQuery();

items.AddRange(listItems);
camlQuery.ListItemCollectionPosition = listItems.ListItemCollectionPosition;

} while (camlQuery.ListItemCollectionPosition != null);
// Returns all records 
var filteritems = items.Where(tt => tt.FieldValues("FileRef").ToString().StartsWith("path to the folder/file to filter out the required items"));
foreach (ListItem item in filteritems)
{
Console.WriteLine(item.FieldValues("FileRef"));
}

}
}
}

Can anyone suggest any way we can overcome this problem or are there any work-arounds to get past this behavior.

Thank you in advance.

tax query – tax_query will produce nothing

I want to display custom type posts from a custom taxonomy category so I did this:

$news = get_posts(
array(
    'posts_per_page' => -1,
    'post_type' => 'news',
    'tax_query' => array(
            'taxonomy' => 'news_category',
            'field' => 'term_id',
            'terms' => 82,
        ),
    )
); 
$postslist = get_posts( $news );    

foreach ($postslist as $crap) { ?>
     <a href="<?php $crap->title; ?>"><?php $crap->title; ?></a>
<?php }?>

I get nothing from this. Why is that? I double checked the category ID and its correct. Also, I used the Custom post types plugin to register the custom taxonomy.

2016 – Content Query webpart shows (blank) for Link List from parent site in SP2016

I have a list of careers links in my parent site (/Lists/careerslinks/AllItems.aspx).
Careers list

I need this list to be displayed in the parent site, and in the careers subsite, but when I create a Content Query webpart, and choose the above list, it displays the links as (blank), rather than with the description.

Displaying as blank

postgresql – How do I speed up a string manipulation query where I want to replace characters, extract certain values and update a table with the results?

You only need a single INSERT and a single UPDATE for the main table. The whole default detection and populating of the “variables” (columns) can be done while inserting the data:

INSERT INTO main_tags (id, tags, variablea, variableb)
select id, tags, var_a, 
       case 
          when var_b is null and 'valA' = any(tags)  then 'valB_default'
          when var_b is null and 'valA_xx' = any(tags) then 'valB_default_two'
          else var_b
       end as var_b
from (
  SELECT id, tags, 
         tags(array_position(tags, 'keyA') + 1) as var_a, 
         tags(array_position(tags, 'keyB') + 1) as var_b
  FROM (
    SELECT id, string_to_array(regexp_replace(tags,'({}")','','g'), ',') as tags
    FROM main
  ) as b 
) x  
WHERE tags && array('keyA','keyB');

The final update can also handle the “unknown” value directly, no need to run three updates:

UPDATE main e
  SET variableA = coalesce(et.variableA, 'unknown'),
      variableB = coalesce(et.variableB, 'unknown')
FROM main_tags et
WHERE e.id = et.id;

One way to speed that up, is to create an index on main_tags (id)


You can actually get rid of the temp table completely and do everything in a single statement. This might or might not be faster, but I think it’s worth trying:

UPDATE main e
  SET variableA = coalesce(et.var_a, 'unknown'),
      variableB = coalesce(et.var_b, 'unknown')
FROM (
  select id, tags, 
         var_a, 
         case 
            when var_b is null and 'valA' = any(tags)  then 'valB_default'
            when var_b is null and 'valA_xx' = any(tags) then 'valB_default_two'
            else var_b
         end as var_b
  from (
    SELECT id, 
           tags, 
           tags(array_position(tags, 'keyA') + 1) as var_a, 
           tags(array_position(tags, 'keyB') + 1) as var_b
    FROM (
      SELECT id, string_to_array(regexp_replace(tags,'({}")','','g'), ',') as tags
      FROM main
    ) as b 
  ) x  
  WHERE tags && array('keyA','keyB')
) as et
WHERE e.id = et.id;

java – Hibernate duplicates results in a Spring JPA query only when Pageable = unpaged

I have a curious Hibernate case running Spring Boot on a JPA repository, where depending on if my Pageable instance is unpaged, I get duplicate results or not…

I construct my Pageable object like this:

Pageable pageable;
if (size > 0) {
    pageable = PageRequest.of(page, size, sort);
} else {
    pageable = Pageable.unpaged()
}

For requests that do have a size, so they are paged, I get my objects back nicely. For Unpaged requests the resulting objects are duplicated.

The underlying query is a bit complex with some fetch joins and a count query for performance:

@Query(value = "select etv from EventTriggerVersion etv "
        + "inner join fetch etv.eventTrigger et "
        + "left join fetch etv.eventRules rules "
        + "left join fetch rules.conditionSets conditionSets "
        + "left join fetch conditionSets.conditions conditions "
        + "left join fetch conditionSets.dayTimeConditions dayTimeConditions "
        + "left join fetch conditions.leftOperand leftOperand "
        + "left join fetch conditions.rightOperand rightOperand "
        + "left join fetch leftOperand.leftOperand leftLeftOperand "
        + "left join fetch leftOperand.rightOperand leftRightOperand "
        + "left join fetch rightOperand.leftOperand rightLeftOperand "
        + "left join fetch rightOperand.rightOperand rightRightOperand "
        + "where etv.latestVersion = true" + " and et.project.id = :projectId "
        + "and (:skipSearch = true or lower(et.name) like lower(concat('%',cast(:search as java.lang.String),'%'))) "
        + "and (cast(:changedAfter as java.util.Date) is null or etv.createdDate >= :changedAfter)",
        countQuery = "select count(etv) from EventTriggerVersion etv "
            + "inner join etv.eventTrigger et "
            + "where etv.latestVersion = true" + " and et.project.id = :projectId "
            + "and (:skipSearch = true or lower(et.name) like lower(concat('%',cast(:search as java.lang.String),'%'))) "
            + "and (cast(:changedAfter as java.util.Date) is null or etv.createdDate >= :changedAfter)"
    )
    Page<EventTriggerVersion> findLatestVersionsInProject(@Param("projectId") UUID projectId,
        @Param("skipSearch") Boolean skipSearch, @Param("search") String search, @Param("changedAfter") Date changedAfter,
        Pageable pageable);

Now I CAN fix this with a distinct in the query and setting @QueryHints(value = @QueryHint(name = HINT_PASS_DISTINCT_THROUGH, value = "false")) but that might not be ideal for performance and I am just curious why this would be necessary only for unpaged pageables.

add RECOMPILE for specific query server wide

I’ve SQL 2017 and i have a query performance problem. i did try to play with all those trace flag (4199,4136,4137,9481)server wide and play with the indexes and stats and the query is still running slow. the only thing that resolve the issue is if i add the OPTION (RECOMPILE) to the query. my problem is that i can’t add it to query itself that comes from the ERP program.can i use it server wide for specific query?

innodb – MySQL Query Fetch Time increases when LEFT JOIN with big table

I am fetching the same amount of data with 2 different queries. However, one of them have a fetching time of ~x130 compared to the other. Being the only difference between the two queries a LEFT JOIN with a big table (4M rows).

Especifically, my problem goes like this:
I have table_a with 200K rows. table_b with 100 rows and table_c with 4M rows. The fields involved are indexed.

My query looks something like this:

       SELECT
          a.id
       FROM
          table_a a
          LEFT JOIN table_b b ON b.id = a.b_id
          LEFT JOIN table_c c ON c.id = b.c_id
       GROUP BY a.id;

MySQL Workbench tells me that this query takes ~4s of Duration and 130 seconds of Fetch Time.
However, when I remove the second LEFT JOIN with the big table, the query takes <1s of Duration and <1s Fetch Time.

I clearly understand why the query duration is increased. I am doing a kinda heavy left join. But, my question is: Why the fetching time is so much higher, if the fetched data is the same?

I have already increased innodb_buffer_pool_size with no success.

I am working in MySQL 8.0.19, with innodb as tables engine.

Is there something I am missing here?
Thanks in advance for the help!

query string – elasticsearch query_string keyword search with or operator not working

I have created a simple reproducer for this issue as following :

PUT trial
{
  "settings": {
    "analysis": {
      "analyzer": {
        "default": {
          "type": "custom",
          "tokenizer": "whitespace",
          "filter": [ "lowercase" ]
        }
      }
    }
  },
  "mappings": {
    "properties": {
      "status": {
        "type": "keyword"
      }
    }
  }
}
PUT trial/_doc/2
{
  "status": "not_installed"
}

PUT trial/_doc/1
{
  "status": "installed"
}
POST trial/_search
{
  "from": 0,
  "size": 20,
  "query": {
    "bool": {
      "filter": [
        {
          "query_string": {
            "query": "not_installed installed",
            "default_operator": "or"
          }
        }
      ]
    }
  }
}

If I execute these commands from top to bottom one by one, I expect doc 1,2 as results. But it’s not getting fetched. What is wrong with my understanding ? Can someone help ?
Working with ES version 7.4

postgresql – How do I get rid of the duplicate code in this query?

I have this query, which works:

SELECT *, ("last done" + "auto time span"::interval) AS "new last done" FROM personal."time tabs" WHERE "reset automatically" = true AND ("last done" + "auto time span"::interval) < now();

However, I notice the duplicate code: ("last done" + "auto time span"::interval). Is it possible to make it only mentioned once?

These did sadly NOT work:

SELECT *, ("last done" + "auto time span"::interval) AS "new last done" FROM personal."time tabs" WHERE "reset automatically" = true AND "new last done" < now();

Or:

SELECT * FROM personal."time tabs" HAVING ("last done" + "auto time span"::interval) AS "new last done" WHERE "reset automatically" = true AND "new last done" < now();

What is the right way?

wp query – Custom Admin Page equivalent to Posts page with custom meta values

I’m creating a custom admin page for CPT (Custom Post Type) in WP-ADMIN returning posts with their taxonomy that should like Posts List Page

Post List edit.php

I am able to display list of posts for a given category, which can be done with a line like: query_posts($query_string . ‘&cat=$term_id’);

followed by: if ( have_posts() ) : while ( have_posts() ) : the_post(); etc. etc. ending with (else)

However this is not what I am looking for exactly. What I am looking for is to achieve something like the attached image with a custom filter that allows me to return certain values for Posts and their meta.

The idea is a website that has videos, the user registers an account and start watching the videos each video he watches, he’ll get 1 point the more videos the more points. What I want is create a “user activity page” that lists all the videos with their taxonomy and 0 points which I filter by user ID or category ID I’ll get the values related.