I'm recently trying to drop Doctrine due to performance and problems with abstraction and move to database driven logic. I'm using mostly PostgreSQL.

Doctrine

One thing that I liked about Doctrine was inheritance, which I used for multiple roles in web app. There is base table/class Person and every role (for example admin, developer, user) extends this class. All users share one base table, so it helps to keep unique login/idenfiticator (in my case it's email). But getting person's info from doctrine resulted in final class, with all it's properties. For example:

$user = $em->getRepository('Entities\Person')->findOneBy(array('email' => 'john.doe@example.com'));
if ( $user instanceof Entities\Developer) {
    ... 
}

Nice feature, but when having many roles, resulting SQL query was very ineffective, selecting from base class left joining all roles and then by defined discriminator mapper builded final class from base table and final table.

PostgreSQL

I discovered that postgres has implemented table inheritance and it works well. But I'd like to simulate Doctrine's behaviour, getting role from db (without knowing it's role and therefore it's final table).

For better examples, my tables look like this:

--
-- base people table
--
CREATE TABLE people
(
  id serial NOT NULL,
  first_name character varying(25) NOT NULL,
  last_name character varying(25) NOT NULL,
  email character varying(50) NOT NULL,
  "password" character varying(150),
  CONSTRAINT people_pkey PRIMARY KEY (id)
);


--
-- role developer (does not have any role specific info)
-- 

CREATE TABLE developer
(
-- Inherited from table people:  id integer NOT NULL DEFAULT nextval('people_id_seq'::regclass),
-- Inherited from table people:  first_name character varying(25) NOT NULL,
-- Inherited from table people:  last_name character varying(25) NOT NULL,
-- Inherited from table people:  email character varying(50) NOT NULL,
-- Inherited from table people:  "password" character varying(150),
  CONSTRAINT developer_pkey PRIMARY KEY (id)
)
INHERITS (people);

--
-- role user
-- 

CREATE TABLE installer
(
-- Inherited from table people:  id integer NOT NULL DEFAULT nextval('people_id_seq'::regclass),
-- Inherited from table people:  first_name character varying(25) NOT NULL,
-- Inherited from table people:  last_name character varying(25) NOT NULL,
-- Inherited from table people:  email character varying(50) NOT NULL,
  client character varying(50),
-- Inherited from table people:  "password" character varying(150),
  CONSTRAINT installer_pkey PRIMARY KEY (id)
)
INHERITS (people);

Solution 1 -> 2 queries

It's quite simple to find out role from base table people and then select directly from role's table:

-- returns name of table (and role) 'developer'
SELECT pg.relname 
FROM people p, pg_class pg
WHERE pg.oid=p.tableoid and p.email = 'john.doe@example.com';

-- getting roles full info
SELECT *
FROM developer
WHERE email = 'kracmar@dannax.sk';

This solution is fine, but I was searching for nicer solution.

Solution 2 -> 1 query using procedure

It would be nice to get info about user in just single query. I went deep into docs of functions and dig something out, but couldn't reach finish. I thought that using return query would be way, but my problem is that I need to specify type of result to function, but it can change depending on user's role (different table with number of columns and types).

This is one of results, function returns record but it's not query, single column with all fields in it separated with comma.

    CREATE OR REPLACE FUNCTION get_person_by_email(person_email VARCHAR) 
            RETURNS record 
            LANGUAGE plpgsql 
            STABLE STRICT AS
    $BODY$
            DECLARE 
                    role varchar;
                    result record;

            BEGIN

                    SELECT pg.relname 
                    INTO role
                    FROM people p,
                            pg_class pg
                    WHERE pg.oid=p.tableoid
                            AND p.email = person_email;

                    IF NOT FOUND THEN
                            RAISE exception 'Person with email % does not exists开发者_C百科.', person_email;
                    END IF;

                    CASE 
                            WHEN role = 'developer' THEN
                                    SELECT * 
                                    INTO result 
                                    FROM developer
                                    WHERE email = person_email;
                            WHEN ROLE = 'installer' THEN
                                    SELECT * 
                                    INTO result
                                    FROM installer
                                    WHERE email = person_email;

                    END CASE;

                    RETURN result;
            END;
    $BODY$;

Selecting from this function is no go as there is missing columns definition. Maybe I complicate things and should use solution 1, but that way I won't learn anything. Any help will be appreciated.

I suppose you might be interested in zyxist's Doctrine2 fork: