Page MenuHomePhabricator

provide some sample common-case queries
Closed, InvalidPublic

Description

... showing how several common use-cases were done in old wb_terms and how they would be done in new schema

Event Timeline

alaa_wmde added a comment.EditedApr 24 2019, 8:21 AM

Looking up all terms of a property/item

[OLD] wb_terms

## For a property with id P123

SELECT term_language, term_type, term_text
FROM wb_terms
WHERE term_full_entity_id = 'P123'


## For an item with id Q321

SELECT term_language, term_type, term_text, term_language
FROM wb_terms
WHERE term_full_entity_id = 'Q321'

[NEW] new schema

## For a property with id P123

SELECT
	wbxl_language 	as term_language,
	wby_name 		as term_type,
	wbx_text 		as 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
;


## For an item with id Q321

SELECT
	wbxl_language 	as term_language,
	wby_name 		as term_type,
	wbx_text 		as term_text
FROM wbt_item_terms
	INNER JOIN wbt_term_in_lang ON wbit_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
	wbit_item_id = 321
;
alaa_wmde added a comment.EditedApr 24 2019, 8:29 AM

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_name        as term_type,
    wbx_text        as 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'
;

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%'
;
Restricted Application added a project: User-Ladsgroup. · View Herald TranscriptApr 24 2019, 8:56 AM
alaa_wmde added a comment.EditedApr 24 2019, 8:58 AM

@Ladsgroup would appreciate a quick review on the provided query examples here. three use cases are provided, each in a comment

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

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

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?

alaa_wmde closed this task as Invalid.Apr 24 2019, 6:58 PM

Follow on T221767