Mailing List Archive: Wikipedia: Wikitech

SQL

Index | Next | Previous | View Threaded

heldergeovane at gmail

Sep 5, 2009, 7:13 AM

Post #1 of 5 (766 views)
Permalink

SQL

Hello!

I was looking at this code
http://svn.wikimedia.org/viewvc/mediawiki/trunk/extensions/intersection/DynamicPageList.php?view=markup

and trying to figure out how to change this piece of code
----
if ('lastedit' == $sOrderMethod)
$sSqlWhere .= ' ORDER BY page_touched ';
else
$sSqlWhere .= ' ORDER BY c1.cl_timestamp ';

$sSqlWhere .= $sSqlOrder;
----
in such way that the extension could order the list alphabetically.

I imagine it would be a simple change, like this:
----
switch ($sOrderMethod)
{
case 'lastedit':
$sSqlWhere .= ' ORDER BY page_touched ';
break;
case 'alphabetical':
$sSqlWhere .= ' ORDER BY WHAT? ';
break;
case 'categoryadd':
default:
$sSqlWhere .= ' ORDER BY c1.cl_timestamp ';
break;
}
----

But I don't know what to use in the SQL instead of the "WHAT?".
Does anybody knows what should it be?

Thanks in advance!

Helder
_______________________________________________
Wikitech-l mailing list
Wikitech-l [at] lists
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

roan.kattouw at gmail

Sep 5, 2009, 7:42 AM

Post #2 of 5 (731 views)
Permalink

Re: SQL [In reply to]

2009/9/5 Helder Geovane Gomes de Lima <heldergeovane [at] gmail>:
> But I don't know what to use in the SQL instead of the "WHAT?".
> Does anybody knows what should it be?
>
You should probably use "ORDER BY page_namespace, page_title" there,
which will sort by namespace, then by title.

Roan Kattouw (Catrope)

_______________________________________________
Wikitech-l mailing list
Wikitech-l [at] lists
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

gmane at kennel17

Sep 7, 2009, 4:23 AM

Post #3 of 5 (701 views)
Permalink

Re: SQL [In reply to]

"Roan Kattouw" <roan.kattouw [at] gmail> wrote in message
news:f154f3a80909050742i90f5949vf0fad7962a154935 [at] mail
> 2009/9/5 Helder Geovane Gomes de Lima <heldergeovane [at] gmail>:
>> But I don't know what to use in the SQL instead of the "WHAT?".
>> Does anybody knows what should it be?
>>
> You should probably use "ORDER BY page_namespace, page_title" there,
> which will sort by namespace, then by title.

However, it should be noted that page_namespace is the numeric index for the
namespace, NOT the textual representation of it, therefore in all languages
they will be ordered as follows (with the namespace names being the
appropriate ones for the language being used):

Foo
Talk:Foo
User:Foo
User talk:Foo
Project:Foo
Project talk:Foo
File:Foo.jpg
File talk:Foo.jpg
MediaWiki:Foo
MediaWiki talk:Foo
Template:Foo
Template talk:Foo
Help:Foo
Help talk:Foo
Category:Foo
Category talk:Foo

Rather than the following 'properly alphabetical' order (assuming
language=en):

Category talk:Foo
Category:Foo
File talk:Foo.jpg
File:Foo.jpg
Foo
Help talk:Foo
Help:Foo
MediaWiki talk:Foo
MediaWiki:Foo
Project talk:Foo
Project:Foo
Talk:Foo
Template talk:Foo
Template:Foo
User talk:Foo
User:Foo

This is probably desirable, but I thought it worth pointing out...

- Mark Clements (HappyDog)

_______________________________________________
Wikitech-l mailing list
Wikitech-l [at] lists
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

qli at ica

Nov 26, 2009, 7:00 AM

Post #4 of 5 (536 views)
Permalink

Re: SQL [In reply to]

hi.sorry to borther again

I want to ask how to rewrite those statement ,too.

Like '>,<,LIMIT '.

And when I use 'order by ' how to change the order of 'ASC,DESC'?

Thanks very much.

vanessa lee

-----Original Message-----
From: Roan Kattouw <roan.kattouw [at] gmail>
To: Wikimedia developers <wikitech-l [at] lists>
Date: Thu, 26 Nov 2009 13:32:10 +0100
Subject: Re: [Wikitech-l] SQL

2009/11/26 Tim Starling <tstarling [at] wikimedia>:
> Don't use subselects, they're not supported by MySQL 4.0 which is what
> we target.
>
> $dbr = wfGetDB( DB_SLAVE );
>
> $max = $dbr->selectField(
> 'recentchanges',
> 'max(rc_id)',
> false,
> __METHOD__,
> array( 'GROUP BY' => 'rc_title' );
>
> $res = $dbr->select(
> 'recentchanges',
> '*',
> array(
> 'rc_id' => $max,
> 'rc_namespace' => 0,
> 'rc_title' => 'Wiki',
> ),
> __METHOD__ );
>
Note that the GROUP BY condition in the first query is unnecessary,
and that the whole thing could be rewritten to SELECT * FROM
recentchanges WHERE rc_namespace=0 AND rc_title='Wiki' ORDER BY rc_id
DESC LIMIT 1;

Roan Kattouw (Catrope)

_______________________________________________
Wikitech-l mailing list
Wikitech-l [at] lists
https://lists.wikimedia.org/mailman/listinfo/wikitech-l
_______________________________________________
Wikitech-l mailing list
Wikitech-l [at] lists
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

roan.kattouw at gmail

Nov 26, 2009, 7:14 AM

Post #5 of 5 (528 views)
Permalink

Re: SQL [In reply to]

2009/11/26 李琴 <qli [at] ica>:
> hi.sorry to borther again
>
> I want to ask how to rewrite those statement ,too.
>
> Like '>,<,LIMIT '.
>
> And when I use 'order by ' how to change the order of 'ASC,DESC'?
>
LIMIT and ORDER BY are similar to GROUP BY, which Tim already
demonstrated. ASC and DESC can just be appended to the order by
column, like 'ORDER BY' => 'rc_id DESC' . Note that ASC is the default
and never has to be specified explicitly. Tim also demonstrated how to
do equalities (like 'rc_namespace' => 0 ); other conditions go in the
same array, but as one string (like 'rc_namespace != 0', 'rc_namespace
< 2' and 'rc_namespace=page_namespace' ).

There's also lots of examples in the MediaWiki codebase, just grep for
'select('.

Roan Kattouw (Catrope)

_______________________________________________
Wikitech-l mailing list
Wikitech-l [at] lists
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads