enWikiquote: Articles on Pages not connected to items list

Fork of enWikiquote: New articles in the past two weeks by Djm-leighpark

This query is marked as a draft This query has been published by Djm-leighpark.

New  articles. on Articles on Pages not connected to items list.  This is optimised for download as a wikitable and is even better when changed to a sortable wikitable by changing class="wikitable" to class="wikitable sortable".  The default sort order is date of creation descending.

One key use is identifying new articles not associated with a wikidata item.  the flags column helps idetifify any already under Prod or VfD so time doesn't have to be wasted looking at those if that is not your purpose.

On enWQ performance is a TBD.  Articles marked as #SheSaid are also noted.

The 'ọ' flag notes marked for #SheSaid in the 2022 campaign, 'C', 'D', 'P' ,'V' flags indicated associated page is in the Copyvio, speedy Delete, Proposed deletion, or Votes for deletion process that might lead to deletion. (Introduced 26 December 2022 20:51 UTC)  The 'N' flag indicates in the Category "Articles with no corresponding English Wikipedia article"

SQL

SELECT
   CONCAT('"',
          NVL((SELECT 'V'
              WHERE EXISTS (SELECT 1
                            FROM    templatelinks tm1
                            JOIN    linktarget lt1 ON lt1.lt_id=tm1.tl_target_id 
                            WHERE   tm1.tl_from=page_id AND tm1.tl_from_namespace=0
                            AND     lt1.lt_title = 'Vfd-new'
                           )),'-'),
          NVL((SELECT 'P'
              WHERE EXISTS (SELECT 1
                            FROM    templatelinks tm1
                            JOIN    linktarget lt1 ON lt1.lt_id=tm1.tl_target_id 
                            WHERE   tm1.tl_from=page_id AND tm1.tl_from_namespace=0
                            AND     lt1.lt_title = 'Dated_prod'
                           )),'-'),
          NVL((SELECT 'D'
              WHERE EXISTS (SELECT 1
                            FROM    templatelinks tm1
                            JOIN    linktarget lt1 ON lt1.lt_id=tm1.tl_target_id 
                            WHERE   tm1.tl_from=page_id AND tm1.tl_from_namespace=0
                            AND      lt1.lt_title IN ('Delete', 'Db')
                           )),'-'),
          NVL((SELECT 'C'
              WHERE EXISTS (SELECT 1
                            FROM    templatelinks tm1
                            JOIN    linktarget lt1 ON lt1.lt_id=tm1.tl_target_id 
                            WHERE   tm1.tl_from=page_id AND tm1.tl_from_namespace=0
                            AND     lt1.lt_title = 'Copyvio'
                           )),'-'),
          NVL((SELECT 'N'
               WHERE EXISTS (SELECT 1
            	 			 FROM    categorylinks cl1
            				 WHERE   cl1.cl_from = page_id
            				 AND     cl1.cl_to = 'Articles_with_no_corresponding_English_Wikipedia_article'
                            )),'-'),
         NVL((SELECT 'ọ' WHERE EXISTS(SELECT 1
                                       FROM comment_revision
                                       JOIN revision x ON x.rev_comment_id=comment_id
                                       WHERE x.rev_page=page_id
                                       AND comment_text RLIKE '.*#[Ss]he[Ss]aid.*'
                                      )),'-'),
        '"') AS "Flags",
  CONCAT('[[',REPLACE(page_title,'_',' '),']]') AS "Articles without an item",
  IF(ISNULL(pp_value),pp_value,CONCAT('[[:d:',pp_value,']]')) AS "Wikidata item",
  actor_name AS "Creator",
  CONCAT( MID(r.rev_timestamp,1,4),'-',MID(r.rev_timestamp,5,2),'-',MID(r.rev_timestamp,7,2),' ',
          MID(r.rev_timestamp,9,2),':',MID(r.rev_timestamp,11,2),':',MID(r.rev_timestamp,13,2)) AS "Date created",
  (SELECT r2.rev_len
   FROM   revision r2
   WHERE  r2.rev_id = (SELECT MAX(r3.rev_id)
                            FROM   revision r3
                            WHERE  r3.rev_page=page_id
                           )
   ) AS "Length"
FROM
  page 
    JOIN revision r ON r.rev_page=page_id
    JOIN actor_revision ON r.rev_actor=actor_id
    LEFT JOIN page_props ON pp_page = page_id AND pp_propname = 'wikibase_item'
WHERE rev_id, rev_timestamp in (SELECT rev_page, MIN(rev_timestamp)
         				        FROM   revision
       				            LEFT JOIN page ON rev_page=page_id
			                    WHERE page_namespace = 0
			                    AND   page_is_redirect = 0
                                GROUP BY rev_page, rev_timestamp
                               )
AND    pp_value IS NULL
AND    page_namespace=0
AND    page_is_redirect = 0
ORDER BY rev_timestamp DESC;

By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...