CODEWITHSUNDEEP
sparql
Ania David
Ania David

Reputation: 1198

SPARQL limit the result for each value of a varible

This is the minimum data required to reproduce the problem

@prefix : <http://example.org/rs#>
@prefix rdfs: <http://www.w3.org/2000/01/rdf-schema#>
@prefix rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>

:artist1 rdf:type :Artist .
:artist2 rdf:type :Artist .
:artist3 rdf:type :Artist .
:en rdf:type :Language .
:it rdf:type :Language .
:gr rdf:type :Language .

:c1
    rdf:type :CountableClass ;
    :appliedOnClass :Artist ;
    :appliedOnProperty :hasArtist
.

:c2
    rdf:type :CountableClass ;
    :appliedOnClass :Language ;
    :appliedOnProperty :hasLanguage
.

:i1
    rdf:type :RecommendableClass ;
    :hasArtist :artist1 ;
    :hasLanguage :en
.

:i2
    rdf:type :RecommendableClass ;
    :hasArtist :artist1 ;
    :hasLanguage :en
.


:i3
    rdf:type :RecommendableClass;
    :hasArtist :artist1 ;
    :hasLanguage :it
.

:i4
    rdf:type :RecommendableClass;
    :hasArtist :artist2 ;
    :hasLanguage :en
.


:i5
    rdf:type :RecommendableClass;
    :hasArtist :artist2 ;
    :hasLanguage :it
.

:i6
    rdf:type :RecommendableClass;
    :hasArtist :artist3 ;
    :hasLanguage :gr
.


:ania :likes :i1 .
:ania :likes :i3 .
:ania :likes :i4 .

This is my query

PREFIX  :     <http://example.org/rs#>
PREFIX  rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX  rdf:  <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX  rs:   <http://spektrum.ctu.cz/ontologies/radio-spectrum#>

SELECT  ?item ?count ?value
WHERE
  { ?item rdf:type :RecommendableClass
    { SELECT  ?countableProperty ?value (count(*) AS ?count)
      WHERE
        { VALUES ?user { :ania }
          VALUES ?countableConfiguration { :c1 }
          ?user :likes ?x .
          ?countableConfiguration :appliedOnProperty ?countableProperty .
          ?countableConfiguration :appliedOnClass ?countableClass .
          ?x ?countableProperty ?value .
          ?value rdf:type ?countableClass
        }
      GROUP BY ?countableProperty ?value
      ORDER BY DESC(?count)
      LIMIT   3
    }
    FILTER NOT EXISTS {?user :likes ?item}
  }

This is the result:

enter image description here

As you see, there're three items that have value artist1 and three other that have artist2

is there any way so i can limit the result to just 2 for each them

Upvotes: 2

Views: 1302

Answers (1)

Joshua Taylor
Joshua Taylor

Reputation: 85813

First some minimal data, with three artists, and some items for each one. I always stress the point of minimal data on Stack Overflow, because it's important for isolating the problem. In this case, you've still provided a relatively large query and a lot more data that we need. Since we know the problem is in how to group artists that are each related to a number of items, all the data needs here is some artists that are related to a number of items. Then we can retrieve them easily, and group them easily.

@prefix : <urn:ex:> .

:artist1 :p :a1, :a2, :a3, :a4 .
:artist2 :p :b2, :b2, :b3, :b4, :b5 .
:artist3 :p :c2 .

Now, you can select artists and their items, and you can determine an index for each item. This method checks for each item how many other items there are that are less than equal to it (there's always at least one equal to it (itself), so the counts are essentially a 1-based index).

prefix : <urn:ex:>

select ?artist ?item (count(?item_) as ?pos){
  ?artist :p ?item_, ?item .
  filter (str(?item_) <= str(?item))
}
group by ?artist ?item

-------------------------
| artist   | item | pos |
=========================
| :artist1 | :a1  | 1   |
| :artist1 | :a2  | 2   |
| :artist1 | :a3  | 3   |
| :artist1 | :a4  | 4   |
| :artist2 | :b2  | 1   |
| :artist2 | :b3  | 2   |
| :artist2 | :b4  | 3   |
| :artist2 | :b5  | 4   |
| :artist3 | :c2  | 1   |
-------------------------

Now you can use having to filter on the position, so that you get at most two per artist:

prefix : <urn:ex:>

select ?artist ?item {
  ?artist :p ?item_, ?item .
  filter (str(?item_) <= str(?item))
}
group by ?artist ?item
having (count(?item_) < 3)

-------------------
| artist   | item |
===================
| :artist1 | :a1  |
| :artist1 | :a2  |
| :artist2 | :b2  |
| :artist2 | :b3  |
| :artist3 | :c2  |
-------------------

References

Doing "n per each x" queries in SPARQL is kind of challenge, and there's no great solution for it yet. Some related reading that might help (be sure to check the comments on these questions and answers, too), include:

Upvotes: 7

Related Questions