[Wikidata-bugs] [Maniphest] [Commented On] T221747: provide some sample common-case queries

2019-04-24 Thread alaa_wmde 
alaa_wmde added a comment.


  yeah that would be great to include as real-life sample queries! can you add 
few of them here in separate comments that I can follow up and add new queries 
too pls?
  
  do the ones provided above constitute good general samples though?

TASK DETAIL
  https://phabricator.wikimedia.org/T221747

EMAIL PREFERENCES
  https://phabricator.wikimedia.org/settings/panel/emailpreferences/

To: Ladsgroup, alaa_wmde
Cc: Aklapper, Lea_Lacroix_WMDE, alaa_wmde, Nandana, Lahi, Gq86, 
GoranSMilovanovic, QZanden, LawExplorer, _jensen, rosalieper, Wikidata-bugs, 
aude, Lydia_Pintscher, JeroenDeDauw, Mbch331
___
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs

[Wikidata-bugs] [Maniphest] [Commented On] T221747: provide some sample common-case queries

2019-04-24 Thread Ladsgroup 
Ladsgroup added a comment.


  Hey,
  I can check the actual logs in tendril and get you some real queries. How 
does that sound?

TASK DETAIL
  https://phabricator.wikimedia.org/T221747

EMAIL PREFERENCES
  https://phabricator.wikimedia.org/settings/panel/emailpreferences/

To: Ladsgroup
Cc: Aklapper, Lea_Lacroix_WMDE, alaa_wmde, Nandana, Lahi, Gq86, 
GoranSMilovanovic, QZanden, LawExplorer, _jensen, rosalieper, Wikidata-bugs, 
aude, Lydia_Pintscher, JeroenDeDauw, Mbch331
___
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs

[Wikidata-bugs] [Maniphest] [Commented On] T221747: provide some sample common-case queries

2019-04-24 Thread alaa_wmde 
alaa_wmde added a comment.


  @Ladsgroup would appreciate a quick review on the provided query examples 
here.
  
  We will add a line about how the provided samples are not necessarily the 
most optimized version and one can try to optimize their queries with 
sub-queries and so on when possible .. so no need to worry on most performant 
queries here, they are just an example to get their heads around the new schema 
and required joins

TASK DETAIL
  https://phabricator.wikimedia.org/T221747

EMAIL PREFERENCES
  https://phabricator.wikimedia.org/settings/panel/emailpreferences/

To: Ladsgroup, alaa_wmde
Cc: Aklapper, Lea_Lacroix_WMDE, alaa_wmde, Nandana, Lahi, Gq86, 
GoranSMilovanovic, QZanden, LawExplorer, _jensen, rosalieper, Wikidata-bugs, 
aude, Lydia_Pintscher, JeroenDeDauw, Mbch331
___
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs

[Wikidata-bugs] [Maniphest] [Commented On] T221747: provide some sample common-case queries

2019-04-24 Thread alaa_wmde 
alaa_wmde added a comment.


  Lookup entity ids by label or alias
  ---
  
  **[OLD]**
  
## Get property ids of properties with a label or alias starting with 
'hell' across languages

SELECT DISTINCT term_full_entity_id
FROM wb_terms
WHERE
term_entity_type = 'property'
AND term_type   IN ( 'label', 'alias' )
AND term_text LIKE 'hell%'
;
  
  **[NEW]**
  
## Get property ids of properties with a label or alias starting with 
'hell' across languages

SELECT DISTINCT ( CONCAT( 'P', wbpt_property_id ) ) as term_full_entity_id
FROM wbt_property_terms
INNER JOIN wbt_term_in_lang ON wbpt_term_in_lang_id = wbtl_id
INNER JOIN wbt_type ON wbtl_type_id = wby_id
INNER JOIN wbt_text_in_lang ON wbtl_text_in_lang_id = wbxl_id
INNER JOIN wbt_text ON wbxl_text_id = wbx_id
WHERE
wby_name   IN ( 'label', 'alias' )
AND wbx_text LIKE 'hell%'
;

TASK DETAIL
  https://phabricator.wikimedia.org/T221747

EMAIL PREFERENCES
  https://phabricator.wikimedia.org/settings/panel/emailpreferences/

To: alaa_wmde
Cc: Aklapper, Lea_Lacroix_WMDE, alaa_wmde, Nandana, Lahi, Gq86, 
GoranSMilovanovic, QZanden, LawExplorer, _jensen, rosalieper, Wikidata-bugs, 
aude, Lydia_Pintscher, JeroenDeDauw, Mbch331
___
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs

[Wikidata-bugs] [Maniphest] [Commented On] T221747: provide some sample common-case queries

2019-04-24 Thread alaa_wmde 
alaa_wmde added a comment.


  Retrieve labels of entity in a specific language
  
  
  **[OLD] wb_terms**
  
SQL

## For a property with id P123, in 'en-gb' language

SELECT term_language, term_type, term_text
FROM wb_terms
WHERE
term_full_entity_id = 'P123'
AND term_type   = 'label'
AND term_language   = 'en-gb'
  
  **[New] new schema**
  
SQL

## For a property with id P123, in 'en-gb' language

SELECT
wbxl_language   as term_language,
wby_nameas term_type,
wbx_textas term_text
FROM wbt_property_terms
INNER JOIN wbt_term_in_lang ON wbpt_term_in_lang_id = wbtl_id
INNER JOIN wbt_type ON wbtl_type_id = wby_id
INNER JOIN wbt_text_in_lang ON wbtl_text_in_lang_id = wbxl_id
INNER JOIN wbt_text ON wbxl_text_id = wbx_id
WHERE
wbpt_property_id = 123
AND wby_name = 'label'
AND wbxl_language = 'en-gb'
;

TASK DETAIL
  https://phabricator.wikimedia.org/T221747

EMAIL PREFERENCES
  https://phabricator.wikimedia.org/settings/panel/emailpreferences/

To: alaa_wmde
Cc: Aklapper, Lea_Lacroix_WMDE, alaa_wmde, Nandana, Lahi, Gq86, 
GoranSMilovanovic, QZanden, LawExplorer, _jensen, rosalieper, Wikidata-bugs, 
aude, Lydia_Pintscher, JeroenDeDauw, Mbch331
___
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs