Searching for member properties from the frontend (with speed ) - Extending umbraco

umbraco Forum » Extending umbraco » Extending umbraco » Searching for member properties from the frontend (with speed )

Searching for member properties from the frontend (with speed )

Options

Previous Topic · Next Topic

kalpa

Posted: Thursday, January 25, 2007 4:25:26 PM

Rank: Fanatic

Joined: 7/19/2006
Posts: 496
Location: Göteborg, Sweden

Hi!

I have batch imported about 700 members (resellers actually) with some custom properties like phone number, city, postal code etc...

Now I would like to make a "Reseller Locator" aka a search form that lists "members" that has matching properties for City and/or Name and/or area code.

I looked at the source code for memberSearch from the umbDashboard nd noticed that the search is a straight SQL query to the DB, isn't there any methods that could be used to do this search instead?

My dream would be something like a SearchMembersByCustomProperties(string propertyalias, string propertyvalue) that uses a SQL "select like" but I guess I'll have to do this myself if no one already has...!?

I also noticed that in the member search there was checkbox that where labeled "Extended search (slow)", does that mean that it will be really slow to search among 700+ members for all members with the generic property "City" containing "%sto%"!?

In a custom DB I would do something like "SELECT Name, City, MapUrl FROM resellers where City LIKE @SearchTerm" And give @SearchTerm the value "%" + SearchTerm + "%"

Would the same thing be slow to perform in umbraco since I'm forced to do some Inner Joins etc!? or is the speed warning mentioned above only to indicate that the opisite is much faster??

// Kalle

" - Yeah I'd like to share your point of view, as long as it's my view too... (http://www.d-a-d.dk/lyrics/pointofview)

kenny

Posted: Thursday, January 25, 2007 4:51:00 PM

Rank: Aficionado

Joined: 8/12/2006
Posts: 139
Location: Norway

Here are some stored procs you might find useful:

Code:

CREATE PROCEDURE [dbo].[x_list_member_groups]
AS

SET NOCOUNT ON

select
n.id,
n.trashed,
n.parentid,
n.nodeuser,
n.level,
n.path,
n.sortorder,
n.uniqueid,
n.text,
n.nodeobjecttype,
n.createdate,
(select count(*) from cmsmember2membergroup t where t.membergroup = m2g.membergroup) member_count
from cmsmember2membergroup m2g
join umbraconode n on n.id = m2g.membergroup
GO

Code:

CREATE PROCEDURE [dbo].[x_list_member_types]
AS

SET NOCOUNT ON

select
n.id,
n.trashed,
n.parentid,
n.nodeuser,
n.level,
n.path,
n.sortorder,
n.uniqueid,
n.text,
n.nodeobjecttype,
n.createdate,
(select count(*) from cmscontent c where c.contenttype = n.id) member_count
from umbraconode n
where n.nodeobjecttype = '9B5416FB-E72F-45A9-A07B-5A9A2709CE43'
GO

Code:

CREATE PROCEDURE [dbo].[x_list_members]
AS

SET NOCOUNT ON

select *
from cmsmember m
join umbraconode mn on mn.id = m.nodeid
GO

Code:

CREATE PROCEDURE [dbo].[x_list_members_by_group]
@group_id INT
AS

SET NOCOUNT ON

select *
from cmsmember m
join umbraconode mn on mn.id = m.nodeid
left join cmsmember2membergroup m2g on m2g.member = m.nodeid
where m2g.membergroup = @group_id
GO

Code:

CREATE PROCEDURE [dbo].[x_list_members_by_type]
@type_id INT
AS

SET NOCOUNT ON

select
*
from cmsmember m
join umbraconode mn on mn.id = m.nodeid
join cmscontent c on c.nodeid = m.nodeid
join umbraconode cn on cn.id = c.contenttype
where cn.nodeobjecttype = '9B5416FB-E72F-45A9-A07B-5A9A2709CE43'
and cn.id = @type_id -- m.nodeid = 1245
GO

Code:

CREATE PROCEDURE [dbo].[x_list_user_groups]
AS

SET NOCOUNT ON

select
*
member_count
from umbracousergroup ug
GO

Code:

CREATE PROCEDURE [dbo].[x_list_users_by_group]
@group_id INT
AS

SET NOCOUNT ON

select *
from umbracouser u
left join umbracouser2usergroup u2g on u2g.[user] = u.id
where u2g.usergroup = @group_id
GO

Kenneth Solberg - xeed* - core dev - level 2 cert pro - my blog

kalpa

Posted: Friday, January 26, 2007 10:59:44 AM

Rank: Fanatic

Joined: 7/19/2006
Posts: 496
Location: Göteborg, Sweden

Kenny!

Thanks a lot for your nice SPs ;)

But in the name of easy distributable solutions I would very much prefer not to use this approach.

Does anyone know if there is a large performance difference between a Stored Procedure and a regular SQL query with inner joins?

// Kalle

" - Yeah I'd like to share your point of view, as long as it's my view too... (http://www.d-a-d.dk/lyrics/pointofview)

Users browsing this topic

Guest

umbraco Forum » Extending umbraco » Extending umbraco » Searching for member properties from the frontend (with speed )

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.