Subqueries

Discussion forum for Extension Writers regarding Extension Development.
Post Reply
combuster
Registered User
Posts: 342
Joined: Sun Oct 26, 2008 11:58 pm

Subqueries

Post by combuster »

Hi,

as far as I have read subqueries are not allowed in phpBB because some DBMS do not support them - right?

How can I accomplish this without subquery:

I have 2 tables:
A with columns: 'id' and 'name'
B with columns: 'id' and 'a_id'

I want to delete all from A where A.id does not exist in (SELECT a_id FROM B)

with subquery, I could do it:

Code: Select all

$sql = 'DELETE FROM A
		WHERE NOT EXISTS (
			SELECT 1 FROM B
			WHERE B.a_id = A.id
		)';
$db->sql_query($sql);
But how should I do it, without a subquery?

Thanks for help!!

User avatar
Brf
Support Team Member
Support Team Member
Posts: 51997
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: Subqueries

Post by Brf »

Code: Select all

Delete from tableA where id not in (select tableA_id from tableB)

combuster
Registered User
Posts: 342
Joined: Sun Oct 26, 2008 11:58 pm

Re: Subqueries

Post by combuster »

Thanks, but you are using a subquery as well... or do you mean that this is allowed in phpBB?

User avatar
Brf
Support Team Member
Support Team Member
Posts: 51997
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: Subqueries

Post by Brf »

Well, if you cannot use a subquery, you would have to turn the subquery into a string.

combuster
Registered User
Posts: 342
Joined: Sun Oct 26, 2008 11:58 pm

Re: Subqueries

Post by combuster »

Thats my question: Am I allowed to use subqueries in phpBB DBAL?

Paul
Infrastructure Team Leader
Infrastructure Team Leader
Posts: 26132
Joined: Sat Dec 04, 2004 3:44 pm
Location: The netherlands.
Name: Paul Sohier
Contact:

Re: Subqueries

Post by Paul »

combuster wrote:Thats my question: Am I allowed to use subqueries in phpBB DBAL?
As long it is supported in all databases types, yes.
Knock knock
Race condition
Who's there?

My BlogMy Photosmy phpBB Extensionscustom phpBB work & Development

combuster
Registered User
Posts: 342
Joined: Sun Oct 26, 2008 11:58 pm

Re: Subqueries

Post by combuster »

ok, I will just run two queries.. I don't know which DBALs must be supported in future and there seems to be no concept for subqueries in phpBB.

Code: Select all

$used_ids = $this->get_used_ids(); // SELECT id FROM.... -> result put in array

// delete all that are not used
$sql = 'DELETE FROM ' . $this->table_prefix . 'some_table
		WHERE id NOT IN ('.join(",", $used_ids).')';
$this->db->sql_query($sql);

Post Reply

Return to “Extension Writers Discussion”