Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2006
    Posts
    8

    Unanswered: “dynamically” JOIN custom user profile properties as additional “columns“

    Hi,

    I need a way to “dynamically” JOIN custom user profile properties as additional “columns“ in my user select query. So I have a table containing basic user data (Users), a table containing a list of available profile properties (ProfileDefinitions) and a table containing the values of the ProfileProperties for each user (UserProfiles).

    Tables:
    • Users(UserID, Username)
    • ProfileDefinitions(ProfileDefinitionID, Name)
    • UserProfiles(UserProfileID, UserID, ProfileDefinitionID, PropertyValue)

    Some sample data:

    Users:
    (1, “john”)
    (2, “jack”)
    (3, “james”)

    ProfileDefinitions:
    (1, “FirstName”)
    (2, “LastName”)

    UserProfiles:
    ( 1, 1, 1, “John”)
    ( 2, 1, 2, ”Locke”)
    (3, 2, 1, “Jack”)
    (4, 2, 2, “Shephard”)
    (5, 3, 1, “James”)
    (6, 3, 2, “Sawyer”)

    A “static” query that produces the result I need:

    PHP Code:
    SELECT U.UserIDU.UserNameP1.PropertyValue AS FirstNameP2.PropertyValue AS LastName
    FROM Users 
    AS U
    LEFT JOIN 
        UserProfiles 
    AS P1 ON U.UserID P1.UserID AND P1ProfileDefinitionID 1
    LEFT JOIN 
        UserProfiles 
    AS P2 ON U.UserID P2.UserID AND P2ProfileDefinitionID 
    The desired output:

    (UserID, UserName, FirstName, LastName)
    (1, “john”, “John”, “Locke”)
    (2, “jack”, “Jack”, “Shephard”)
    (3, “james”, “James”, “Sawyer”)

    So off course the number ProfileDefinitions is variable and what I basically need is an SQL statement that returns the data in the Users table with an additional column for each ProfileDefinition, compatible with MS SQL 2000. (Not all users have all ProfileDefinitions filled in.)

    It could be that I’m completely off here and that there’s a far more easier/better way to achieve similar results, so please forgive me for my ignorance.

    Please find the table CREATE scripts below:

    PHP Code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    IF NOT EXISTS (SELECT FROM sys.objects WHERE object_id OBJECT_ID(N'[dbo].[ProfileDefinitions]') AND type in (N'U'))
    BEGIN
    CREATE TABLE 
    [dbo].[ProfileDefinitions](
        [
    ProfileDefinitionID] [intIDENTITY(1,1NOT NULL,
        [
    Name] [nvarchar](50NOT NULL,
     
    CONSTRAINT [PK_ProfileDefinitionsPRIMARY KEY CLUSTERED 
    (
        [
    ProfileDefinitionIDASC
    )WITH (PAD_INDEX  OFFIGNORE_DUP_KEY OFFON [PRIMARY]
    ON [PRIMARY]
    END
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    IF NOT EXISTS (SELECT FROM sys.objects WHERE object_id OBJECT_ID(N'[dbo].[Users]') AND type in (N'U'))
    BEGIN
    CREATE TABLE 
    [dbo].[Users](
        [
    UserID] [intIDENTITY(1,1NOT NULL,
        [
    UserName] [nvarchar](50NOT NULL,
     
    CONSTRAINT [PK_UsersPRIMARY KEY CLUSTERED 
    (
        [
    UserIDASC
    )WITH (PAD_INDEX  OFFIGNORE_DUP_KEY OFFON [PRIMARY]
    ON [PRIMARY]
    END
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    IF NOT EXISTS (SELECT FROM sys.objects WHERE object_id OBJECT_ID(N'[dbo].[UserProfiles]') AND type in (N'U'))
    BEGIN
    CREATE TABLE 
    [dbo].[UserProfiles](
        [
    UserProfileID] [intIDENTITY(1,1NOT NULL,
        [
    UserID] [intNOT NULL,
        [
    ProfileDefinitionID] [intNOT NULL,
        [
    PropertyValue] [nvarchar](50NOT NULL,
     
    CONSTRAINT [PK_UserProfilesPRIMARY KEY CLUSTERED 
    (
        [
    UserProfileIDASC
    )WITH (PAD_INDEX  OFFIGNORE_DUP_KEY OFFON [PRIMARY]
    ON [PRIMARY]
    END
    GO
    IF NOT EXISTS (SELECT FROM sys.foreign_keys WHERE object_id OBJECT_ID(N'[dbo].[FK_UserProfiles_ProfileDefinitions]') AND parent_object_id OBJECT_ID(N'[dbo].[UserProfiles]'))
    ALTER TABLE [dbo].[UserProfiles]  WITH CHECK ADD  CONSTRAINT [FK_UserProfiles_ProfileDefinitionsFOREIGN KEY([ProfileDefinitionID])
    REFERENCES [dbo].[ProfileDefinitions] ([ProfileDefinitionID])
    GO
    ALTER TABLE 
    [dbo].[UserProfilesCHECK CONSTRAINT [FK_UserProfiles_ProfileDefinitions]
    GO
    IF NOT EXISTS (SELECT FROM sys.foreign_keys WHERE object_id OBJECT_ID(N'[dbo].[FK_UserProfiles_Users]') AND parent_object_id OBJECT_ID(N'[dbo].[UserProfiles]'))
    ALTER TABLE [dbo].[UserProfiles]  WITH CHECK ADD  CONSTRAINT [FK_UserProfiles_UsersFOREIGN KEY([UserID])
    REFERENCES [dbo].[Users] ([UserID])
    GO
    ALTER TABLE 
    [dbo].[UserProfilesCHECK CONSTRAINT [FK_UserProfiles_Users
    Thanks in advance,

    rudgr

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Dear me, looks like a real poor design to me (and only now are you starting to realise that)!

    Change your table design and these problems all but dissapear

    Remember, there's nothing wrong with storing NULL values in your tables!
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2006
    Posts
    8
    Hi George, unfotunately this the design of DNN, so I'm affraid I can't change that

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is there a set number of profile definitions?
    George
    Home | Blog

  5. #5
    Join Date
    Mar 2006
    Posts
    8
    hi george, no the number of profile properties can vary per portal. i.e. the profile properties can be "user defined" by the portal amdinistrator ;-(
    Last edited by rudgr; 10-06-08 at 06:52.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What determines that profile property 1 is FirstName and 2 is LastName?
    George
    Home | Blog

  7. #7
    Join Date
    Mar 2006
    Posts
    8
    Hi George,

    The profile fields are defined in the ProfileDefinitions table.

    So if an administrator desides he also needs to collect CompanyName data the record below is added in the ProfileDefinitions table:

    (3, “CompanyName ”)

    Thanks!

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    There's no easy way of doing this without resorting to dynamic SQL...

    ...well, no way I am aware of anyway.
    George
    Home | Blog

  9. #9
    Join Date
    Mar 2006
    Posts
    8
    thanks george, kind of was affraid of that...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •