UserProperties SQL Table

This forum is only for questions or discussions about working with the mojoPortal source code in Visual Studio, obtaining the source code from the repository, developing custom features, etc. If your question is not along these lines this is not the right forum. Please try to post your question in the appropriate forum.

Please do not post questions about design, CSS, or skinning here. Use the Help With Skins Forum for those questions.

This forum is for discussing mojoPortal development

This forum is only for questions or discussions about working with the mojoPortal source code in Visual Studio, obtaining the source code from the repository, developing custom features, etc. If your question is not along these lines this is not the right forum. Please try to post your question in the appropriate forum.

You can monitor commits to the repository from this page. We also recommend developers to subscribe to email notifications in the developer forum as occasionally important things are announced.

Before posting questions here you might want to review the developer documentation.

Do not post questions about design, CSS, or skinning here. Use the Help With Skins Forum for those questions.
This thread is closed to new posts. You must sign in to post in the forums.
8/5/2010 11:14:28 AM
Gravatar
Total Posts 34

UserProperties SQL Table

Hi,

I had this great idea right at the beginning of a project that I was testing out with Mojo that I'd integrate all of my custom user features into the CMS profile.  Its been a fun journey with the profile, with some of the more interesting bits to do with custom Ajax controls really stumping me, but anyway I finally got there. Earlier, I had already seen the userproperties table and had a "plan" how I would deal with all the properties in there and join them to other tables/databases with other information.

My plan was to use self joins to create a VIEW which I have used in other projects for tables with lots of different things in one table or for hierarchical data with parentId pointing to the same table. So I excited write the code below which works fine and then go to add in DISTINCT or GROUP BY

SELECT
up1.PropertyID,
up1.UserGuid,
up1.PropertyValueString AS FirstName,
up2.PropertyValueString AS LastName,
up3.PropertyValueString AS Availability,
up4.PropertyValueString AS IsVisaRequired,
up5.PropertyValueString AS MinRate,
up6.PropertyValueString AS MaxRate

FROM
dbo.mp_UserProperties up1
JOIN dbo.mp_UserProperties up2 ON up2.UserGuid = up1.UserGuid AND up1.PropertyName = 'First_Name'
JOIN dbo.mp_UserProperties up3 ON up3.UserGuid = up2.UserGuid AND up2.PropertyName = 'Last_Name'
JOIN dbo.mp_UserProperties up4 ON up4.UserGuid = up2.UserGuid AND up3.PropertyName = 'DateOfNextAvailability'
JOIN dbo.mp_UserProperties up5 ON up5.UserGuid = up2.UserGuid AND up4.PropertyName = 'IsVisaRequired'
JOIN dbo.mp_UserProperties up6 ON up6.UserGuid = up2.UserGuid AND up5.PropertyName = 'MinRate'
JOIN dbo.mp_UserProperties up7 ON up7.UserGuid = up2.UserGuid AND up6.PropertyName = 'MaxRate'

 

The problem I have is that the PropertyValueString is nText and it says that you can't sort, group or distinct that type of field!!!!

So, never really having run up against this, is there a way to get rid of the duplicate rows, or should I scrap the whole idea and make a custom  class in C# with all my properties and then run the various SQL commands  and loop through the results and populate my object that way.

My main concern with that is there will be lots of times I will have wanted to do a quick JOIN to this view and it's adding a fairly hefty step in the way with lots of ASP.NET processing and more SQL quieres.

Thoughts? And many thanks!!!

Matt

8/5/2010 1:41:05 PM
Gravatar
Total Posts 1196
Proud member of the mojoPortal team

Help support mojoPortal!
Add-on modules

Re: UserProperties SQL Table

Personally, I think I'd rather make a specific table to store my values, for two reasons: (1) I don't like intermixing my own data with "system" data. (2) It'd be a lot easier! smiley

Now down to brass tacks to see how to make this work. I'm not too familiar with SQL Server (we use Oracle for our main database, and MySQL with mojoPortal). However, for something like this I'd probably create an Oracle PL/SQL function (the equivalent of a T-SQL stored procedure in SQL Server). It would be *something* like this (pseudocode alert!):

Create stored procedure get_prop_val (input guid, input PropName) as
begin
   select NVL(A.PropertyValueString,' ')
     from dbo.mp_UserProperties A
    where A.UserGuid = {guid}
      and A.PropertyName = {PropName};
end;

Then, you could build your SQL statement as something like:

select
up1.PropertyID,
up1.UserGuid,
get_prop_val(up1.UserGuid,'First_Name'),
get_prop_val(up1.UserGuid,'Last_Name'),
get_prop_val(up1.UserGuid,'DateOfNextAvailability'),
get_prop_val(up1.UserGuid,'IsVisaRequired'),
get_prop_val(up1.UserGuid,'MinRate'),
get_prop_val(up1.UserGuid,'MaxRate')
from
dbo.mp_userProperties up1
where
[where clause]

Hope that's useful.
Jamie
 

8/6/2010 3:08:59 AM
Gravatar
Total Posts 34

Re: UserProperties SQL Table

Hi Jamie,

 

Many thanks for the reply.  Almost all the data for the entire application is stored in it's own separate database, but I went with extending the profile for a couple of reasons.

a) I couldn't work out how to remove the profile tab completely, therefore leaving the user with potentially 2 places to go for there profile, even if I removed all the options in it. Can this be done?

b) Once I realised it could be extended and I had a play with it, I knew I could do everything I needed there, so checked the database to see how it was stored.  Once I saw the way it was stored, my heart sank a bit but realised I could use a view to make everything simple again.... Alas not...

Your idea would work, but if I understand it correctly, you will need to pass it the userGuid to return the specific person information.  This is great for most cases, but If say I want to join a table in my database that has 15 people in and get back the data, I'll be passing it 15 sql queries and then adding each line to a gridview for etc.. rather than just passing one and binding the result to it.

I'm really regretting using the profile now as spent a lot of time making controls and AJAX controls to get round when it does a full page post back it loses the tab selection and now may not be able to use it properly for what I want.

I did even "think" about changing the DB column to varchar :-) But not sure what else that would break and know you shouldn't do stuff like that, because of upgrades etc.

Any other idea Jamie, Joe or anyone :-)

 

Thanks again for taking the time to reply, mucho appreciated as I am behind schedule now so really don't want to redo this part again :-(

Matt

8/7/2010 7:00:58 AM
Gravatar
Total Posts 18439

Re: UserProperties SQL Table

I would recommend against this approach, it will become a worse and worse solution the more custom fields you add. It would be far better to pull all the data with a simple select statement into a class object. If you really need the data to look like a table then you should implement your own table.

While it is not possible to remove the profile tab, you could remove most of the individual profile settings and then create a UserControl to encapsulate the menagement of your table data. It would need its own save button separate from the main save button of the user profile. You could add the control to the page by implementing ISettingControl and pluging it in just like an individual property control. The difference is that your implementation of ISettingControl would just get and set an unused string. The real data for your custom table would be saved to your table by code you implement behind your own save button. You could wrap all your ui controls inside an UpdatePanel to avoid postback of your control affecting the main page.

In the latest version of mojoPortal for SQL Server, we have changed from ntext to nvarchar(max), but I still recommend staying away from trying to reshape key value fields from user properties into a column per field table structure.

Best,

Joe

8/9/2010 3:19:46 AM
Gravatar
Total Posts 34

Re: UserProperties SQL Table

Hi Joe,

Thanks for the reply.  I managed to get round the issue by CONVERT the columns on the fly and it then lets you group buy, which I didn't expect.  I should have made custom controls for the standard text as well I guess and saved to my own tables, as I have made custom ones for multi checkbox, dropdowns and also used the AJAX file upload so not to leave the page when uploading files

In case it helps anyone else, I managed to get all of my controls to update to the database (except AJAX)  when you press the built in update button just by catching the selected index changed event on my dropdowns and multicheck boxes.  I assume you could do something similar if you did your own date and text ones too, to save having 2 update buttons.

Thanks for the help as always.

Matt

You must sign in to post in the forums. This thread is closed to new posts.