Query to grab all children of an asset

(Rhulse) #1

I need a small script that will grab all the children of an asset of type 'x' and return a result set to loop over.




I think asset_manager->getChildren is the function to use, but I only want live items and I also want a list of other places each asset is linked.







cheers,



Richard

(Dan Simmons) #2

You want to get all children of any asset in the system of the specified type? Why do you want to do this (I'm curious)?


Not quite what you want (a getChildren solution), but SQL comes to mind:


    
    SELECT
      minorid
    
    FROM
      sq_ast_lnk
    
    WHERE
      -- Children of all assets of the specified type
      sq_ast_lnk.majorid IN (SELECT assetid FROM sq_ast WHERE type_code = 'folder')
    
      -- Only TYPE_1 and TYPE_2 links
      AND sq_ast_lnk.link_type < 4;


For debugging purposes you can get more information about the returned assets:

    
    SELECT
      sq_ast.assetid,
      sq_ast.name,
      sq_ast.type_code,
      sq_ast_lnk.link_type

FROM
  sq_ast_lnk

LEFT JOIN
  -- For getting extra information about the asset
  sq_ast ON sq_ast_lnk.minorid = sq_ast.assetid

WHERE

  -- Children of all assets of the specified type
  sq_ast_lnk.majorid IN (SELECT assetid FROM sq_ast WHERE type_code = 'folder')

  -- Only TYPE_1 and TYPE_2 links
  AND sq_ast_lnk.link_type < 4;




Just replace 'folder' with the type code you want the children of.
(Rhulse) #3

[quote]
You want to get all children of any asset in the system of the specified type? Why do you want to do this (I'm curious)?

[/quote]



Hi Dan,



What I need is an XML dump of all our current news stories which are in dated folders within a master folder.


    
    
    (each of these has month/day folders too)
    
    stories -
             - 2008 
             - 2009
             - 2010



The XML needs to include where else the stories are linked (these are category folders in our case).

A php script run on our master server is going to be way faster than any asset listing because there are 38,000 stories.

DIrect SQl would be the fastest as I suspect Matrix won't like that much.

The XML is going to be imported into another system for testing purposes.

The script will need to do audio assets under a specified root node too, later on.


cheers,

Richard
(Dan Simmons) #4

Hi Richard,


Have you tried using the import/export scripts in scripts/dev/import/ ?



Haven't had much experience with them myself, but it could be a good starting point (even if you were to customise the export script).



Cheers,

Dan.

(Rhulse) #5

[quote]
Hi Richard,



Have you tried using the import/export scripts in scripts/dev/import/ ?



Haven't had much experience with them myself, but it could be a good starting point (even if you were to customise the export script).



Cheers,

Dan.

[/quote]



I don't have those scripts … aren't they the ones that do Matrix structured XML ?

(Dan Simmons) #6

Yes, they created Matrix structured XML. They are in the scripts/import/ directory that ships with Matrix:
http://public-cvs.squiz.net/cgi-bin/viewcvs.cgi/mysource_matrix/scripts/import/

(Rhulse) #7

[quote]
Yes, they created Matrix structured XML. They are in the scripts/import/ directory that ships with Matrix:

http://public-cvs.squiz.net/cgi-bin/viewcvs.cgi/mysource_matrix/scripts/import/

[/quote]



Thanks. I can probably hack on those to get what I need. The target system is not Matrix…